For print_table, thats easy. Here is the Oracle8i specific one. It uses
authid_current user so you can install it ONCE per database and many people can
use it (with roles and all intact):
SCOTT> create or replace procedure print_table( p_query in varchar2 )
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;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
dbms_output.put_line( '---------------------------------------------------' );
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '---------------------------------------------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/
The above, you would run as:
SCOTT> set serveroutput on
SCOTT> exec print_table( 'select * from emp where ename = ''SCOTT'' ' );
(note the doubling of the quotes for character string constants!!)
---------------------------------------------------
EMPNO : 7788
ENAME : SCOTT
JOB : ANALYST
MGR : 7566
HIREDATE : 19-apr-1987 00:00:00
SAL : 3000
COMM :
DEPTNO : 20
---------------------------------------------------
PL/SQL 처리가 정상적으로 완료되었습니다.
SCOTT> grant execute on print_table to public;
SCOTT> conn system/manager
SYSTEM> create public synonym print_table for scott.print_table;
SYSTEM> exec print_table('select * from v$session_wait where sid=6');
---------------------------------------------------
SID : 6
SEQ# : 14
EVENT : rdbms ipc message
P1TEXT : timeout
P1 : 180000
P1RAW : 0002BF20
P2TEXT :
P2 : 0
P2RAW : 00
P3TEXT :
P3 : 0
P3RAW : 00
WAIT_TIME : 0
SECONDS_IN_WAIT : 1650
STATE : WAITING
---------------------------------------------------
PL/SQL 처리가 정상적으로 완료되었습니다.