plsql - TO_CHAR of an Oracle PL/SQL TABLE type

Question

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;

  -- 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.

Answer 1

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