create or replace

　　procedure print_table

　　( p_query in varchar2,

　　p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )

　　-- this utility is designed to be installed ONCE in a database and used

　　-- by all. Also, it is nice to have roles enabled so that queries by

　　-- DBA's that use a role to gain access to the DBA_* views still work

　　-- that is the purpose of AUTHID CURRENT_USER

　　AUTHID CURRENT_USER

　　is

　　l_theCursor integer default dbms_sql.open_cursor;

　　l_columnValue varchar2(4000);

　　l_status integer;

　　l_descTbl dbms_sql.desc_tab;

　　l_colCnt number;

　　l_cs varchar2(255);

　　l_date_fmt varchar2(255);

　　-- small inline procedure to restore the sessions state

　　-- we may have modified the cursor sharing and nls date format

　　-- session variables, this just restores them

　　procedure restore

　　is

　　begin

　　if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))

　　then

　　execute immediate

　　'alter session set cursor_sharing=exact';

　　end if;

　　if ( p_date_fmt is not null )

　　then

　　execute immediate

　　'alter session set nls_date_format=''' || l_date_fmt || '''';

　　end if;

　　dbms_sql.close_cursor(l_theCursor);

　　end restore;

　　begin

　　-- I like to see the dates print out with times, by default, the

　　-- format mask I use includes that. In order to be "friendly"

　　-- we save the date current sessions date format and then use

　　-- the one with the date and time. Passing in NULL will cause

　　-- this routine just to use the current date format

　　if ( p_date_fmt is not null )

　　then

　　select sys_context( 'userenv', 'nls_date_format' )

　　into l_date_fmt

　　from dual;

　　execute immediate

　　'alter session set nls_date_format=''' || p_date_fmt || '''';

　　end if;

　　-- to be bind variable friendly on this ad-hoc queries, we

　　-- look to see if cursor sharing is already set to FORCE or

　　-- similar, if not, set it so when we parse -- literals

　　-- are replaced with binds

　　if ( dbms_utility.get_parameter_value

　　( 'cursor_sharing', l_status, l_cs ) = 1 )

　　then

　　if ( upper(l_cs) not in ('FORCE','SIMILAR'))

　　then

　　execute immediate

　　'alter session set cursor_sharing=force';

　　end if;

　　end if;

　　-- parse and describe the query sent to us. we need

　　-- to know the number of columns and their names.

　　dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

　　dbms_sql.describe_columns

　　( l_theCursor, l_colCnt, l_descTbl );

　　-- define all columns to be cast to varchar2's, we

　　-- are just printing them out

　　for i in 1 .. l_colCnt loop

　　if ( l_descTbl(i).col_type not in ( 113 ) )

　　then

　　dbms_sql.define_column

　　(l_theCursor, i, l_columnValue, 4000);

　　end if;

　　end loop;

　　-- execute the query, so we can fetch

　　l_status := dbms_sql.execute(l_theCursor);

　　-- loop and print out each column on a separate line

　　-- bear in mind that dbms_output only prints 255 characters/line

　　-- so we'll only see the first 200 characters by my design...

　　while ( dbms_sql.fetch_rows(l_theCursor) > 0 )

　　loop

　　for i in 1 .. l_colCnt loop

　　if ( l_descTbl(i).col_type not in ( 113 ) )

　　then

　　dbms_sql.column_value

　　( l_theCursor, i, l_columnValue );

　　dbms_output.put_line

　　( rpad( l_descTbl(i).col_name, 30 )

　　|| ': ' ||

　　substr( l_columnValue, 1, 200 ) );

　　end if;

　　end loop;

　　dbms_output.put_line( '-----------------' );

　　end loop;

　　-- now, restore the session state, no matter what

　　restore;

　　exception

　　when others then

　　restore;

　　raise;

　　end;

　　SQL> set serverout on size 100000

　　SQL> select * from a;

　　ID COL

　　------ -----

　　1 AA

　　2 bb

　　3 cc

　　SQL> exec print_table('select * from a');

　　ID : 1

　　COL : AA

　　-----------------

　　ID : 2

　　COL : bb

　　-----------------

　　ID : 3

　　COL : cc

　　-----------------

　　PL/SQL procedure successfully completed