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.

why not write a helper function "print_table" that encapsulates the output formatting you'd like, then use it as many times as you wish... — tbone, Jun 07 '11 at 12:46
@tbone: For any type of table? Can that be done with Oracle's `ANYTYPE` data type? — Lukas Eder, Jun 07 '11 at 12:51
wasn't thinking completely generic function for all types, but that's an interesting question...let me noodle on it, i think you're on the right track with anydata/anytype — tbone, Jun 07 '11 at 13:11
That would be awesome! Typesafety is nice, but in this case, it's really too much — Lukas Eder, Jun 07 '11 at 13:24
`FORALL` is solely for doing a bulk in-bind of variables to a SQL statement (e.g. INSERT, UPDATE or DELETE). You just need to use a plain vanilla PL/SQL `FOR` loop. — Jeffrey Kemp, Jan 13 '12 at 07:08
@JeffreyKemp: You're probably right. I don't remember why I chose `FORALL` at the time. — Lukas Eder, Jan 13 '12 at 22:08

tbone · Accepted Answer · 2013-05-28T18:25:10.537

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

Wow, that's what I call pulling a solution by its hair :) XML, I hadn't thought of that. Awesome, and it does work for some cases. Thanks a lot! — Lukas Eder, Jun 08 '11 at 07:21
[Here's a great post](http://orasql.org/2013/04/02/sqlplus-tips-2/) on using xmltable for this kind of trick. Now thanks to the author it also covers a case when query result has null values (see in comments). — Blza Box, Feb 06 '14 at 13:15

TO_CHAR of an Oracle PL/SQL TABLE type

1 Answers1

Linked