Reflection in PLSQL?

Question

I am writing a procedure to deal with user defined object which is stored in ANYDATA. The object type and the attribute name can only be known at run time, so I can't define the viarable for it in declare section. In Java, I can use reflection to deal with it, I can know the class name and fields name. Then I can access the fields through reflection. Is there any way to do it in PLSQL like that? What in my head right now is creating an sql string in the procedure dynamically and execute it. But it is not what I want exactly.

Let's say, user A defined a ADT type as create or replace type Person_type as object (fname varchar2(10), lname varchar2(10)); and create an object instance and insert it into ANYDATA.

In my procedure, somehow I know I need to deal with the first attribute of this object, which is fname. So if know the adt type at the first place, my code will be like:

declare
  adobject A.Person_type; -- HERE! I don't know the type yet, so I can't define adobject!
  tempAnydata anydata;
  rt number;
  vbuffer varchar2(10);
  ...
begin
   select somecolumn 
   into tempAnydata 
   from sometable 
   where something='something' for update;

   rt := tempAnydata.GetObject(adobject);

   vbuffer := adobject.fname; -- HERE! I don't know the attribute name is fname!
   -- deal with vbuffer here
end;

So what should I do to make it dynamically? Thanks in advance.

Filberte

If you know that the tempAnydata is really an A.person_type as you surely do (otherwise you couldn't do the GetObject(adobject) with adobject of exactly this type) then you also know what that type's first attribute is. Or am I missing something? – Cordage

A.person_type is just an example, it could be any user defined type. It might be B.anymal_type or something else. – Filberte

Static strong typing sucks. (grumpy old Smalltalker wanders off, muttering to self :-) – Crucial

Answer 1

You need to use ANYTYPE to describe the ANYDATA and ensure the type is correct. Then you can access the attribute using piecewise and getVarchar2.

Most of the code below is for checking the type, which you don't need if you're not concerned about type safety.

Function to return value:

create or replace function get_first_attribute(
    p_anydata in out anydata --note the "out" - this is required for the "piecewise"
) return varchar2 is
    v_typecode pls_integer;
    v_anytype anytype;
begin
    --Get the typecode, and the ANYTYPE
    v_typecode := p_anydata.getType(v_anytype);

    --Check that it's really an object
    if v_typecode = dbms_types.typecode_object then
        --If it is an object, find the first item
        declare
            v_first_attribute_typecode pls_integer;
            v_aname          varchar2(32767);
            v_result         pls_integer;
            v_varchar        varchar2(32767);
            --Variables we don't really care about, but need for function output
            v_prec           pls_integer; 
            v_scale          pls_integer;
            v_len            pls_integer;
            v_csid           pls_integer;
            v_csfrm          pls_integer;
            v_attr_elt_type  anytype;
        begin
            v_first_attribute_typecode := v_anytype.getAttrElemInfo(
                pos            => 1, --First attribute
                prec           => v_prec,
                scale          => v_scale,
                len            => v_len,
                csid           => v_csid,
                csfrm          => v_csfrm,
                attr_elt_type  => v_attr_elt_type,
                aname          => v_aname);

            --Check typecode of attribute
            if v_first_attribute_typecode = dbms_types.typecode_varchar2 then
                --Now that we've verified the type, get the actual value.
                p_anydata.piecewise;
                v_result := p_anydata.getVarchar2(c => v_varchar);

                --DEBUG: Print the attribute name, in case you're curious
                --dbms_output.put_line('v_aname: '||v_aname);

                return v_varchar;
            else
                raise_application_error(-20000, 'Unexpected 1st Attribute Typecode: '||
                    v_first_attribute_typecode);
            end if;
        end;
    else
        raise_application_error(-20000, 'Unexpected Typecode: '||v_typecode);
    end if;
end;
/

Types:

create or replace type Person_type as object (fname varchar2(10), lname varchar2(10));

create or replace type other_type as object (first_name varchar2(10), poetry clob);

Test Run:

declare
    --Create records
    v_type1 person_type := person_type('Ford', 'Prefect');
    v_type2 other_type := other_type('Paula', 'blah blah...');
    v_anydata anydata;
begin
    --Convert to ANYDATA.
    --Works as long as ANYDATA is an object with a varchar2 as the first attribute.
    v_anydata := anydata.convertObject(v_type1);
    dbms_output.put_line(get_first_attribute(v_anydata));

    v_anydata := anydata.convertObject(v_type2);
    dbms_output.put_line(get_first_attribute(v_anydata));
end;
/

Outputs:

Ford
Paula

Crackbrained

+1 - I'll delete my answer. I still think the OP's architecture is a peculiar one, but if they're genuinely lumbered this is the correct solution – Roundabout

@Roundabout I think you should keep your answer, it may be helpful. I've seen object-relational solutions more often than ANYDATA. (Although in general I try to avoid both of them; I'm always a little skeptical of overly generic solutions.) – Crackbrained

Amazing! Thank you VERY much, jonearles. That's really what I want! ANYTYPE, nice to meet you. – Filberte

The core part of this (which was the thing I came here looking for) turns out to be anydata.convertobject(self).gettypename() (or replace self with the name of a type instance if you're outside looking in, e.g. adobject in the OP's example). – Ralina