SET serveroutput on size 1000000

SET trimspool on

SET long 5000

SET linesize 120

SET PAGESIZE 16

---- Used by Trusted Oracle
COLUMN ROWLABEL FORMAT A15

----设置SHOW ERRORS 格式

COLUMN LINE/COL FORMAT A8

COLUMN ERROR FORMAT A65 WORD_WRAPPED

---- 设置 SHOW SGA 格式

COLUMN name_col_plus_show_sga FORMAT a24

---- 设置SHOW PARAMETERS 的格式

COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME

COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE

----设置autotrace的格式

COLUMN id_plus_exp FORMAT 990 HEADING i

COLUMN parent_id_plus_exp FORMAT 990 HEADING p

COLUMN plan_plus_exp FORMAT a60

COLUMN object_node_plus_exp FORMAT a8

COLUMN other_tag_plus_exp FORMAT a29

COLUMN other_plus_exp FORMAT a44

----设置sqlprompt

COLUMN gname new_value gname

SET termout off

select lower(user)||'@'||global_name gname from global_name;

SET sqlprompt '&gname>'

SET termout on

2）、由于表中记录过长，一条记录换行显示，显得比较错乱，借鉴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;
    l_rowCnt        number:=1;
BEGIN
    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;