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包使用的典范教程，保存一下。
?
?