Oracle: How to get object field value by name dynamically

Question

Is there a way to dynamically get and set the value of an object's field - when the name of the desired field is stored in a variable?

Something like this:

create or replace type t_my_type force as object
(
    num_val_a  number(30),
    num_val_b  number(30)
);

declare
    l_object t_my_type := t_my_type(33, 77);
    l_field_name varchar2(255 char) := 'num_val_b';
begin
    dbms_output.put_line(
        l_object.get_num_value_by_name(l_field_name) 
    );
end;

The idea is that the name of the field has to be calculated - l_field_name

For example, in Javascript, getting the value of an object's field by its name stored in a variable is quite simple - like this : l_object[l_field_name]

How to do this in Oracle PL/SQL?

Answer 1

Is there a way to dynamically get and set the value of an object's field - when the name of the desired field is stored in a variable?

Adapting my answer to this question, to get an attribute of an object, you can create a Reflection package:

CREATE PACKAGE reflection IS
  TYPE type_info IS RECORD(
    prec        PLS_INTEGER,
    scale       PLS_INTEGER,
    len         PLS_INTEGER,
    csid        PLS_INTEGER,
    csfrm       PLS_INTEGER,
    schema_name VARCHAR2(30),
    type_name   VARCHAR2(30),
    version     VARCHAR2(100),
    count       PLS_INTEGER
  );

  TYPE attr_info IS RECORD(
    prec           PLS_INTEGER,
    scale          PLS_INTEGER,
    len            PLS_INTEGER,
    csid           PLS_INTEGER,
    csfrm          PLS_INTEGER,
    attr_elt_type  ANYTYPE,
    aname          VARCHAR2(30)
  );

  FUNCTION get_size(
    p_anydata IN ANYDATA
  ) RETURN PLS_INTEGER;

  FUNCTION get_attr_name_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2;

  FUNCTION get_attr_value_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2;

  FUNCTION get_attr_by_name(
    p_anydata IN ANYDATA,
    p_name    VARCHAR2
  ) RETURN VARCHAR2;
END;
/

CREATE PACKAGE BODY reflection IS
  DEBUG BOOLEAN := FALSE;

  PROCEDURE get_type(
    i_anydata IN  ANYDATA,
    o_typeid  OUT PLS_INTEGER,
    o_anytype OUT ANYTYPE
  )
  IS
  BEGIN
    o_typeid := i_anydata.GetType( typ => o_anytype );
  END;

  FUNCTION is_Object(
    p_typeid  PLS_INTEGER
  ) RETURN BOOLEAN
  IS
  BEGIN
    RETURN p_typeid = DBMS_TYPES.TYPECODE_OBJECT;
  END;

  FUNCTION is_Collection(
    p_typeid  PLS_INTEGER
  ) RETURN BOOLEAN
  IS
  BEGIN
    RETURN p_typeid = DBMS_TYPES.TYPECODE_NAMEDCOLLECTION;
  END;

  FUNCTION get_info(
    p_anytype IN ANYTYPE
  ) RETURN type_info
  IS
    v_typeid    PLS_INTEGER;
    v_type_info REFLECTION.TYPE_INFO;
  BEGIN
    v_typeid := p_anytype.GetInfo (
      v_type_info.prec, 
      v_type_info.scale,
      v_type_info.len, 
      v_type_info.csid,
      v_type_info.csfrm,
      v_type_info.schema_name, 
      v_type_info.type_name, 
      v_type_info.version,
      v_type_info.count
    );

    RETURN v_type_info;
  END;

  FUNCTION get_size(
    p_anydata IN ANYDATA
  ) RETURN PLS_INTEGER
  IS
    v_anytype     ANYTYPE;
    v_typeid      PLS_INTEGER;
  BEGIN
    Get_Type( p_anydata, v_typeid, v_anytype );
    RETURN Get_Info( v_anytype ).COUNT;
  END;

  FUNCTION get_attr_name_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2
  IS
    v_anydata     ANYDATA := p_anydata;
    v_anytype     ANYTYPE;
    v_typeid      PLS_INTEGER;
    v_type_info   REFLECTION.TYPE_INFO;
    v_output      VARCHAR2(4000);
    v_attr_typeid PLS_INTEGER;
    v_attr_info   REFLECTION.ATTR_INFO;
  BEGIN
    Get_Type( v_anydata, v_typeid, v_anytype );
    IF NOT is_Object(v_typeid) THEN
      RAISE_APPLICATION_ERROR(-20000, 'Not an object');
    END IF;
    v_type_info := Get_Info( v_anytype );
    
    IF p_index < 1 OR p_index > v_type_info.COUNT THEN
      RETURN NULL;
    END IF;
    
    v_anydata.PIECEWISE;
    v_attr_typeid := v_anytype.getAttrElemInfo(
      pos            => p_index,
      prec           => v_attr_info.prec,
      scale          => v_attr_info.scale,
      len            => v_attr_info.len,
      csid           => v_attr_info.csid,
      csfrm          => v_attr_info.csfrm,
      attr_elt_type  => v_attr_info.attr_elt_type,
      aname          => v_attr_info.aname
    );
    RETURN v_attr_info.aname;
  END;
        
  FUNCTION get_attr_value_at(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN VARCHAR2
  IS
    v_anydata   ANYDATA := p_anydata;
    v_typeid    PLS_INTEGER;
    v_anytype   ANYTYPE;
    v_type_info REFLECTION.TYPE_INFO;
    v_output    VARCHAR2(4000);
  BEGIN
    Get_Type( v_anydata, v_typeid, v_anytype );
    IF NOT is_Object(v_typeid) THEN
      RAISE_APPLICATION_ERROR(-20000, 'Not an object');
    END IF;
    v_type_info := Get_Info( v_anytype );
    
    IF p_index < 1 OR p_index > v_type_info.COUNT THEN
      RETURN NULL;
    END IF;
    
    v_anydata.PIECEWISE;
    
    FOR i IN 1 .. p_index LOOP
      DECLARE
        v_attr_typeid PLS_INTEGER;
        v_attr_info   REFLECTION.ATTR_INFO;
        v_result_code PLS_INTEGER;
      BEGIN
        v_attr_typeid := v_anytype.getAttrElemInfo(
          pos            => i,
          prec           => v_attr_info.prec,
          scale          => v_attr_info.scale,
          len            => v_attr_info.len,
          csid           => v_attr_info.csid,
          csfrm          => v_attr_info.csfrm,
          attr_elt_type  => v_attr_info.attr_elt_type,
          aname          => v_attr_info.aname
        );

        IF DEBUG THEN
          DBMS_OUTPUT.PUT_LINE(
            'Attribute ' || i || ': '
            || v_attr_info.aname
            || ' (type ' || v_attr_typeid || ')'
          );
        END IF;

        CASE v_attr_typeid
        WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
          DECLARE
            v_value NUMBER;
          BEGIN
            v_result_code := v_anydata.GetNumber( v_value );
            IF i = p_index THEN
              RETURN TO_CHAR( v_value );
            END IF;
          END;
         WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
          DECLARE
            v_value VARCHAR2(4000);
          BEGIN
            v_result_code := v_anydata.GetVarchar2( v_value );
            IF i = p_index THEN
              RETURN v_value;
            END IF;
          END;
         WHEN DBMS_TYPES.TYPECODE_DATE THEN
          DECLARE
            v_value DATE;
          BEGIN
            v_result_code := v_anydata.GetDate( v_value );
            IF i = p_index THEN
              RETURN TO_CHAR( v_value, 'YYYY-MM-DD HH24:MI:SS' );
            END IF;
          END;
        ELSE
          NULL;
        END CASE;
      END;
    END LOOP;
    RETURN NULL;
  END;

  FUNCTION get_attr_by_name(
    p_anydata IN ANYDATA,
    p_name    VARCHAR2
  ) RETURN VARCHAR2
  IS
  BEGIN
    FOR attr_no IN 1 .. REFLECTION.get_size( p_anydata ) LOOP
      IF p_name = REFLECTION.get_attr_name_at( p_anydata, attr_no ) THEN
        RETURN REFLECTION.get_attr_value_at( p_anydata, attr_no );
      END IF;
    END LOOP;
    RETURN NULL;
  END;
END;
/

Then, if you have the object:

create or replace type t_my_type force as object
(
    num_val_a  number(30),
    num_val_b  number(30),
    str_val_c  VARCHAR2(20),
    date_val_d DATE
);

You can convert the object to ANYDATA and get the value from that:

DECLARE
   obj           t_my_type := t_my_type(42, 3, 'xyz', SYSDATE);
   p_anydata     ANYDATA;
BEGIN
  dbms_output.enable;
  
  p_anydata := ANYDATA.ConvertObject( obj );
  DBMS_OUTPUT.PUT_LINE('NUM_VAL_A:  ' || Reflection.get_attr_by_name(p_anydata, 'NUM_VAL_A'));
  DBMS_OUTPUT.PUT_LINE('NUM_VAL_B:  ' || Reflection.get_attr_by_name(p_anydata, 'NUM_VAL_B'));
  DBMS_OUTPUT.PUT_LINE('STR_VAL_C:  ' || Reflection.get_attr_by_name(p_anydata, 'STR_VAL_C'));
  DBMS_OUTPUT.PUT_LINE('DATE_VAL_D: ' || Reflection.get_attr_by_name(p_anydata, 'DATE_VAL_D'));
END;
/

Which outputs:

NUM_VAL_A:  42
NUM_VAL_B:  3
STR_VAL_C:  xyz
DATE_VAL_D: 2024-05-24 11:41:25

I do not know any way to set the attributes of an object dynamically (without modifying the object to include extra methods). You could dynamically create an ANYDATA object using ANYDATA.BEGINCREATE and then the ANYDATA.SET* methods to populate the ANYDATA object but I'm not sure there is a way to convert from ANYDATA back to the concrete object (and if you did that you would be creating a new object instance rather than modifying the existing instance).

fiddle