[20210428]改进pr.sql脚本.txt

[20210428]改进pr.sql脚本.txt --//tanel poder提供一个脚本pr.sql,用来实现显示信息的纵向显示。例子： SCOTT@book> select * from dept where deptno=10   2  @ pr Pivoting output using Tom Kyte's printtab.... ============================== DEPTNO                        : 10 DNAME                         : ACCOUNTING LOC                           : NEW YORK PL/SQL procedure successfully completed. --//第一次看到这样的使用是上https://connor-mcdonald.com/网站，不知道对方如何实现的。后来对方给我一个脚本，好像是windows --//版本我修改一下，适合linux使用。我这才想起来tanel poder的脚本包里面有。 $ cat prxx.sql . set termout off def _pr_tmpfile=/tmp/pr.out store set &_pr_tmpfile.set replace set termout on set serverout on size 1000000 termout off echo off save &_pr_tmpfile replace set termout on 0 c clob := q'\ 0 declare 999999      \';; 999999      l_theCursor     integer default dbms_sql.open_cursor;; 999999      l_columnValue   varchar2(4000);; 999999      l_status        integer;; 999999      l_descTbl       dbms_sql.desc_tab;; 999999      l_colCnt        number;; 999999  begin 999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );; 999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );; 999999      for i in 1 .. l_colCnt loop 999999          dbms_sql.define_column( l_theCursor, i, 999999                                  l_columnValue, 4000 );; 999999      end loop;; 999999      l_status := dbms_sql.execute(l_theCursor);; 999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop 999999          dbms_output.put_line( '==============================' );; 999999          for i in 1 .. l_colCnt loop 999999                  dbms_sql.column_value( l_theCursor, i, 999999                                         l_columnValue );; 999999                  dbms_output.put_line 999999                      ( rpad( l_descTbl(i).col_name, 999999                        30 ) || ': ' || l_columnValue );; 999999          end loop;; 999999      end loop;; 999999  exception 999999      when others then 999999          dbms_output.put_line(dbms_utility.format_error_backtrace);; 999999          raise;; 999999 end;; / set termout off @&_pr_tmpfile.set get &_pr_tmpfile nolist host rm &_pr_tmpfile set termout on --//今天我自己再改一下在每个字段前面加上序号的功能： $ cat prxxn.sql . set termout off def _pr_tmpfile=/tmp/pr.out store set &_pr_tmpfile.set replace set termout on set serverout on FORMAT WRAPPED size 1000000 termout off echo off save &_pr_tmpfile replace set termout on 0 c clob := q'\ 0 declare 999999      \';; 999999      l_theCursor     integer default dbms_sql.open_cursor;; 999999      l_columnValue   varchar2(4000);; 999999      l_status        integer;; 999999      l_descTbl       dbms_sql.desc_tab;; 999999      l_colCnt        number;; 999999  begin 999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );; 999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );; 999999      for i in 1 .. l_colCnt loop 999999          dbms_sql.define_column( l_theCursor, i, 999999                                  l_columnValue, 4000 );; 999999      end loop;; 999999      l_status := dbms_sql.execute(l_theCursor);; 999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop 999999          dbms_output.put_line( '==================================' );; 999999          for i in 1 .. l_colCnt loop 999999                  dbms_sql.column_value( l_theCursor, i, 999999                                         l_columnValue );; 999999                  dbms_output.put_line 999999                      (lpad(i,3)||' '|| rpad( l_descTbl(i).col_name, 999999                        30 ) || ': ' || l_columnValue );; 999999          end loop;; 999999      end loop;; 999999  exception 999999      when others then 999999          dbms_output.put_line(dbms_utility.format_error_backtrace);; 999999          raise;; 999999 end;; / set termout off @&_pr_tmpfile.set get &_pr_tmpfile nolist host rm &_pr_tmpfile set termout on --//注意第一行的点可不是多余的，不要删除。 --//测试如下： SCOTT@book> select * from emp where rownum=1   2  @ prxxn ==================================   1 EMPNO                         : 7369   2 ENAME                         : SMITH   3 JOB                           : CLERK   4 MGR                           : 7902   5 HIREDATE                      : 1980-12-17 00:00:00   6 SAL                           : 800   7 COMM                          :   8 DEPTNO                        : 20 PL/SQL procedure successfully completed.