how to get list of columns of an record type or list of columns for an Object type in Oracle-CodePudding

If we create an object type or recrod type like below

create type t_data as object(
 execId varchar2(500),
 description varchar2(500)
 );
 /

then how to get the list of columns for this type at later point?. In case of tables we could use all_tab_columns to get similar list.

CodePudding user response：

You can use user_type_attrs:

SELECT * 
FROM user_type_attrs
WHERE TYPE_NAME = 'T_DATA';

Column ATTR_NAME is what you are looking for.

Here is a demo

CodePudding user response：

You can write a package to perform reflection on an object instance:

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 list_attrs(
    p_anydata IN ANYDATA
  ) RETURN SYS.ODCIVARCHAR2LIST PIPELINED;
END;
/

With the body:

CREATE PACKAGE BODY reflection IS
  DEBUG BOOLEAN := FALSE;

  FUNCTION get_type(
    p_anydata IN ANYDATA
  ) RETURN ANYTYPE
  IS
    v_typeid    PLS_INTEGER;
    v_anytype   ANYTYPE;
    v_type_info REFLECTION.TYPE_INFO;
  BEGIN
    v_typeid := p_anydata.GetType( typ => v_anytype );
    RETURN v_anytype;
  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
    );

    IF v_typeid <> DBMS_TYPES.TYPECODE_OBJECT THEN
      RAISE_APPLICATION_ERROR( -20000, 'Not an object.' );
    END IF;

    RETURN v_type_info;
  END;

  FUNCTION get_size(
    p_anydata IN ANYDATA
  ) RETURN PLS_INTEGER
  IS
  BEGIN
    RETURN Get_Info( Get_Type( p_anydata ) ).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_type_info   REFLECTION.TYPE_INFO;
    v_output      VARCHAR2(4000);
    v_attr_typeid PLS_INTEGER;
    v_attr_info   REFLECTION.ATTR_INFO;
  BEGIN
    v_anytype := Get_Type( v_anydata );
    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_anytype   ANYTYPE;
    v_type_info REFLECTION.TYPE_INFO;
    v_output    VARCHAR2(4000);
  BEGIN
    v_anytype := Get_Type( v_anydata );
    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 list_attrs(
    p_anydata IN ANYDATA
  ) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
  IS
  BEGIN
    FOR attr_no IN 1 .. REFLECTION.get_size(p_anydata)
    LOOP
      PIPE ROW (REFLECTION.get_attr_name_at(p_anydata, attr_no));
    END LOOP;
  END list_attrs;
END;
/

Then if you have the type:

CREATE TYPE test_obj AS OBJECT(
  A VARCHAR2(20),
  B NUMBER,
  C DATE
);

You can use:

SELECT *
FROM   TABLE(
         REFLECTION.list_attrs(
           ANYDATA.ConvertObject(test_obj('A', 1, SYSDATE))
         )
       );

Which outputs:

COLUMN_VALUE

A

B

C

COLUMN_VALUE
A
B
C

db<>fiddle here