A simple pipelined version of print_table
Posted by Matthias Rogel on 10. April 2013
Tom Kyte’s print_table procedure, available on
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958#14442395195806
seems to be very popular and there exist tricky variations on the theme, for example the following nice xml-trick by Sayan Malakshinov.
Please note that it is very easy to use the existing print_table-code to generate a pipelined version which can be used in SQL.
I use the following code since ages and it always does me a great job, so probably it is worth sharing.
create or replace function fprint_table ( p_query in varchar2, p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' ) return sys.odcivarchar2list authid current_user pipelined is l varchar2(4000); s integer default 1; begin dbms_output.enable(buffer_size => null); print_table( p_query => p_query, p_date_fmt => p_date_fmt ); loop dbms_output.get_line(line => l, status => s); exit when s != 0; begin pipe row(l); exception when no_data_needed then exit; end; end loop; return; end fprint_table; / sokrates@11.2 > select * from table(fprint_table('select user,sysdate from dual')); USER : SOKRATES SYSDATE : 10-apr-2013 12:27:50 ----------------- 1 row selected.
Leave a comment