Print_Table
想把报表的某一行数据直排得打印出来，发现ask tom上面已经有现成的代码了，贴出来看一下，写得真好，适用性极强。
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);
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;
执行结果：
SQL> exec print_table('select * from v$database');
DBID : 485689964
NAME : DODO
CREATED : 19-jan-2009 15:10:30
RESETLOGS_CHANGE# : 1
RESETLOGS_TIME : 19-jan-2009 14:35:24
PRIOR_RESETLOGS_CHANGE# : 0
PRIOR_RESETLOGS_TIME :
LOG_MODE : ARCHIVELOG
CHECKPOINT_CHANGE# : 234242
ARCHIVE_CHANGE# : 125009
CONTROLFILE_TYPE : CURRENT
CONTROLFILE_CREATED : 19-jan-2009 15:10:30
CONTROLFILE_SEQUENCE# : 210
CONTROLFILE_CHANGE# : 234242
CONTROLFILE_TIME : 23-feb-2009 10:04:50
OPEN_RESETLOGS : NOT ALLOWED
VERSION_TIME : 19-jan-2009 15:10:30
OPEN_MODE : READ WRITE
PROTECTION_MODE : MAXIMUM PERFORMANCE
PROTECTION_LEVEL : MAXIMUM PERFORMANCE
REMOTE_ARCHIVE : ENABLED
ACTIVATION# : 485692838
DATABASE_ROLE : PRIMARY
ARCHIVELOG_CHANGE# : 222873
SWITCHOVER_STATUS : SESSIONS ACTIVE
DATAGUARD_BROKER : DISABLED
GUARD_STATUS : NONE
SUPPLEMENTAL_LOG_DATA_MIN : NO
SUPPLEMENTAL_LOG_DATA_PK : NO
SUPPLEMENTAL_LOG_DATA_UI : NO
FORCE_LOGGING : NO
-----------------
PL/SQL procedure successfully completed
主要用DBMS_SQL代替了自己写动态SQL和各种操作的复杂性，真是一个DBMS_SQL包使用的典范教程，保存一下。