Sokrates on Oracle

A simple pipelined version of print_table

Posted by Matthias Rogel on 10. April 2013

Tom Kyte’s print_table procedure, available on
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
l varchar2(4000);
s integer default 1;
  dbms_output.enable(buffer_size => null);
     p_query => p_query,
     p_date_fmt => p_date_fmt

     dbms_output.get_line(line => l, status => s);
     exit when s != 0;
        pipe row(l);
     exception when no_data_needed then exit;
  end loop;

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 Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: