开发者

TO_CHAR of an Oracle PL/SQL TABLE type

For debugging purposes, I'd like to be able to "TO_CHAR" an Oracle PL/SQL in-memory table. Here's a simplified example, of what I'd like to do:

DECLARE
  TYPE T IS TABLE OF MY_TABLE%ROWTYPE INDEX BY PLS_INTEGER;
  V T;

BEGIN
  -- ..

  -- Here, I'd like to dbms_output V's contents, which of course doesn't compile
  FOR i IN V.FIRST .. V.LAST LOOP
    dbms_output.put_line(V(i));
  END LOOP;

  -开发者_JS百科- I want to omit doing this:
  FOR i IN V.FIRST .. V.LAST LOOP
    dbms_output.put_line(V(i).ID || ',' || V(i).AMOUNT ...);
  END LOOP;

END;

Can this be achieved, simply? The reason I ask is because I'm too lazy to write this debugging code again and again, and I'd like to use it with any table type.


ok, sorry this isn't complete, but to followup with @Lukas, here's what I have so far:

First, instead of trying to create anydata/anytype types, I tried using XML extracted from a cursor...weird, but its generic:

CREATE OR REPLACE procedure printCur(in_cursor IN sys_refcursor) IS
begin

    FOR c IN (SELECT ROWNUM rn,
                    t2.COLUMN_VALUE.getrootelement () NAME,
                    EXTRACTVALUE (t2.COLUMN_VALUE, 'node()') VALUE
               FROM TABLE (XMLSEQUENCE (in_cursor)) t,
                    TABLE (XMLSEQUENCE (EXTRACT (COLUMN_VALUE, '/ROW/node()'))) t2
               order by 1)

   LOOP
      DBMS_OUTPUT.put_line (c.NAME || ': ' || c.VALUE);
   END LOOP;

exception
    when others then raise;
end;
/

Now, to call it, you need a cursor, so I tried casting to cursor in pl/sql, something like:

open v_cur for select * from table(cast(v_tab as tab_type));

But depending on how v_tab is defined, this may or may not cause issues in pl/sql cast (using %rowtype in nested table def seems to give issues).

Anyway, you can build on this or refine it as you like. (and possibly use xmltable...)

Hope that helps

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜