Oracle 将集合（对象表）转储到 clob 或 xml 中

【问题标题】：Oracle dumping collection (table of objects) into clob or xmlOracle 将集合（对象表）转储到 clob 或 xml 中
【发布时间】：2022-11-28 23:51:35
【问题描述】：

我有 MY_OBJ_TABLE 类型，想将此类的可变内容转储为文本或 xml 格式。

问题是，处理此类请求的函数应该能够接收任何类型的 table of objects ，而不仅仅是 MY_OBJ_TABLE 。

我调查了 passAnyObject.sql ，这看起来是朝着正确方向迈出的一步。非常感谢建议和解决方案。

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;

【解决方案1】：

您无法使用 ANYDATA 实现您的目标。

您可以使用 ANYDATA 和反射将单个对象解析为字符串，如 my previous answer 中所述。

但是，如果你想解析一个集合，那么：

您可以使用 ANYDATA.ConvertCollection( your_collection ); 将其转换为 ANYDATA
然后在处理它时，您可以将评估模式设置为 PIECEWISE ( documentation )，这样您就可以遍历各个元素...
但是没有 Get* 方法（ documentation ）允许您将元素提取为抽象 ANYDATA 类型；你必须将其提取为具体的基础类型，因此您创建的任何过程都必须知道您在编译时传入的集合的数据类型。

我确实尝试解决该问题，但该功能不支持抽象解决方案。据我所知：

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

然后：

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

然后你可以使用以下方法解析你的对象：

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

您会注意到包体中间的 MY_OBJ 类型必须是硬编码的，并且 Oracle 文档中没有支持抽象解决方案的解决方案。

fiddle

【讨论】：

谢谢您的回答;这在调试和快速检查变量内容时非常有用，但是硬编码阻止它成为通用解决方案。
@mpapec 正如我所说，您不能使用 ANYDATA 来创建通用解决方案，这可能意味着您的问题没有解决方案。
我想也没有解决方案去 xmltype(obj) 路线？
好吧，从实际的角度来看，有一个专门的函数来转储特定的对象表是有意义的（不管这会导致大量这样的散落）。

分享于2022年11月29日 oracle plsql 问答