从网上找到的一个脚本,自己改了改.

create or replace procedure print_table
(qry in varchar2)
as
q_cursor         integer;
column_val       varchar2(256);
tretval          integer;
col_cnt          integer;
desc_t           dbms_sql.desc_tab;
error_position   integer;
fileid           utl_file.file_type;
filedir          varchar2(250);
filename         varchar2(250);
t_query          varchar2(4000);
tstr             varchar2(200);
statement_error  exception;
pragma exception_init(statement_error,-921);
begin
  t_query := ltrim(rtrim(qry));
  dbms_output.enable(1000000);
  if substr(t_query,length(t_query),1) in (';','/') then
     t_query := substr(t_query,1,length(t_query)-1);
  end if;
  q_cursor  := dbms_sql.open_cursor;
  dbms_sql.parse(q_cursor,t_query,dbms_sql.v7);
  error_position := dbms_sql.last_error_position;
  dbms_sql.describe_columns(q_cursor,col_cnt,desc_t);
  for i in 1 .. col_cnt loop
    dbms_sql.define_column(q_cursor,i,column_val,255);
  end loop;
  tretval := dbms_sql.execute(q_cursor);
  loop
    tretval := dbms_sql.fetch_rows(q_cursor);
    if tretval = 0 then
       exit;
    end if;
    for i in 1 .. col_cnt loop
        dbms_sql.column_value(q_cursor, i,column_val);
        dbms_output.put_line(rpad(desc_t(i).col_name,30,' ') || ':'||nvl(column_val,'空值'));
    end loop;
    dbms_output.put_line('-------------结束-------------');
  end loop;
  exception when others then
  error_position := dbms_sql.last_error_position;
  raise_application_error(-20001,sqlcode||' '||sqlerrm||' Error at '||error_position);
end;
/

set serveroutput on
exec print_table('select * from tab where rownum < 3')

过程已创建。

SQL>
SQL> set serveroutput on
SQL> exec print_table('select * from tab where rownum < 3')
TNAME                         :AAA
TABTYPE                       :TABLE
CLUSTERID                     :空值
-------------结束-------------
TNAME                         :AQ$DEF$_AQCALL
TABTYPE                       :VIEW
CLUSTERID                     :空值
-------------结束-------------

PL/SQL 过程已成功完成。