Use of stored procedures-print_table

Print_table [SQL] www.2cto.com 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 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 = exist'; 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 provided des 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; -- 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); merge (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; www.2cto.com [SQL] SQL> set serverout on size 100000 SQL> select * from; 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