Oracle dumping collection (table of objects) into clob or xml-CodePudding

I have MY_OBJ_TABLE type and would like to dump variable content of such either into text or xml format.

The thing is, function processing such request should be able to receive any type of table of objects, not just the MY_OBJ_TABLE.

I have looked into passAnyObject.sql which looks like a step in the right direction. Advices and solutions are greatly appreciated.

CREATE OR REPLACE TYPE "MY_OBJ" FORCE AS OBJECT (
    key     VARCHAR2(20),
    value   VARCHAR2(1000),
    CONSTRUCTOR FUNCTION MY_OBJ RETURN SELF AS RESULT,
    MEMBER PROCEDURE init_my_obj
);
CREATE OR REPLACE TYPE BODY "MY_OBJ" AS
    CONSTRUCTOR FUNCTION MY_OBJ RETURN SELF AS RESULT
        AS
    BEGIN
        init_my_obj ();
        return;
    END MY_OBJ;
    MEMBER PROCEDURE init_my_obj
        AS
    BEGIN
        key := NULL;
        value := NULL;
    END init_my_obj;
END;

CREATE OR REPLACE TYPE MY_OBJ_TABLE IS
    TABLE OF MY_OBJ;

CodePudding user response：

You cannot achieve your goal using ANYDATA.

You can parse an individual object to a string using ANYDATA and reflection as described in my previous answer.

However, if you want to parse a collection then:

you can convert it to ANYDATA using ANYDATA.ConvertCollection( your_collection );
then when processing it you can set the evaluation mode to PIECEWISE (documentation) which will let you iterate through individual elements...
but there are no Get* methods (documentation) that allow you to extract an element as an abstract ANYDATA type; you MUST extract it as the concrete underlying type so any procedure you create must know the data type of the collection that you are passing in at compile time.

I did try to work through the problem but the functionality is not there to support an abstract solution. This is as far as I got:

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_Object_At(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN ANYDATA;

  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;
END;
/

Then:

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_Object_At(
    p_anydata IN ANYDATA,
    p_index   IN PLS_INTEGER DEFAULT 1
  ) RETURN ANYDATA
  IS
    v_anydata     ANYDATA := p_anydata;
    v_anytype     ANYTYPE;
    v_typeid      PLS_INTEGER;
  BEGIN
    Get_Type( v_anydata, v_typeid, v_anytype );
    IF NOT is_Collection(v_typeid) THEN
      RAISE_APPLICATION_ERROR(-20000, 'Not a collection');
    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;
        v_value       ANYDATA;
        v_object      MY_OBJ;
      BEGIN
        v_result_code := v_anydata.GetObject( v_object );
        v_value := ANYDATA.ConvertObject( v_object );
        
        -- TODO: there is no Get* function that returns an ANYDATA type that
        -- would allow abstract parsing to continue. For example:
        --
        -- v_result_code := v_anydata.Get( v_value );
        --
        -- You would need to use:
        -- 
        -- DECLARE
        --   v_object MY_OBJ;
        -- BEGIN
        --   v_result_code := v_anydata.GetObject( v_object );
        --   v_value := ANYDATA.ConvertObject( v_object );
        -- END;
        --
        -- But that hard-codes the concrete object type.
        IF i = p_index THEN
          RETURN v_value;
        END IF;
      END;
    END LOOP;

    RETURN NULL;
  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;
END;
/

Then you can parse your object using:

DECLARE
   objs          MY_OBJ_TABLE;
   idx           PLS_INTEGER := 1;
   p_anydata     ANYDATA;
   p_attr_name   VARCHAR2(30);
   p_attr_value  VARCHAR2(4000);
   p_element     ANYDATA;
BEGIN
  dbms_output.enable;
  objs := MY_OBJ_TABLE(
    MY_OBJ('a', 'one'),
    MY_OBJ('b', 'two'),
    MY_OBJ('c', 'three')
  );

  p_anydata := ANYDATA.ConvertCollection( objs );
  -- Still not worked out how to get the collection size from the ANYDATA.
  FOR i IN 1 .. objs.count LOOP
    DECLARE
      p_element ANYDATA := REFLECTION.get_Object_at(p_anydata, i);
    BEGIN
      DBMS_OUTPUT.PUT_LINE( 'My Obj ' || i || ':' );
      FOR attr_no IN 1 .. REFLECTION.get_size( p_element ) LOOP
        p_attr_name  := REFLECTION.get_attr_name_at( p_element, attr_no );
        p_attr_value := REFLECTION.get_attr_value_at( p_element, attr_no );
        DBMS_OUTPUT.PUT_LINE( '  ' || p_attr_name || ': ' || p_attr_value );
      END LOOP;
    END;
  END LOOP;
END;
/

You will note in the middle of the package body the MY_OBJ type has to be hard-coded and there is not a solution in the Oracle documentation to support an abstract solution.

fiddle

CodePudding user response：

If you want an XML solution then:

CREATE TYPE MY_OBJ AS OBJECT (
    key     VARCHAR2(20),
    value   VARCHAR2(1000)
);

CREATE TYPE MY_OBJ_TABLE IS TABLE OF MY_OBJ;

Then you can use:

SELECT XMLELEMENT(
         "OBJECTS",
         XMLAGG(
           XMLTYPE(VALUE(t))
         )
       ).getClobVal() AS output
FROM   TABLE(
         MY_OBJ_TABLE(
           MY_OBJ('a', 'one'),
           MY_OBJ('b', 'two')
         )
       ) t;

Which outputs:

OUTPUT
<OBJECTS><MY_OBJ> <KEY>a</KEY> <VALUE>one</VALUE> </MY_OBJ> <MY_OBJ> <KEY>b</KEY> <VALUE>two</VALUE> </MY_OBJ> </OBJECTS>

fiddle