[20191104]sqlplus 管道檔案 過濾.txt

[20191104]sqlplus 管道檔案 過濾.txt

--//前一段時間知道連結
--//裡面pr.sql指令碼如何實現的.
--//實際上受限sqlplus的顯示限制,有時候確實需要某種過濾功能.比如如果顯示資訊太多,使用pr.sql指令碼檢視也是不是很方便.
--//實際上mysql就有這個功能,透過定義pager實現過濾.
--//很久以前我寫過一篇連結:http://blog.itpub.net/267265/viewspace-1062965/=>[20131211]mysql pager定義＝vim.txt
--//我一直很想在sqlplus實現類似的功能,連結:
--//http://blog.itpub.net/267265/viewspace-2285749/=>[20181219]script使用小技巧.txt
--//http://blog.itpub.net/267265/viewspace-2375095/=>[20190108]rlwrap sqlplus tee相關問題.txt
--//http://blog.itpub.net/267265/viewspace-2375436/=>[20190110]rlwrap sqlplus tee相關問題3.txt
--//今天測試看看是否透過管道實現,最大的問題就是快取的問題.透過例子說明:

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試
$ mknod  /tmp/oop.pipe p
$ ls -l /tmp/oop.pipe
prw-r--r-- 1 oracle oinstall 0 2019-11-04 10:44:02 /tmp/oop.pipe

--//seesion 1:
SCOTT@book> spool /tmp/oop.pipe
SCOTT@book> select * from dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

--//seesion 2:
$ tail -f /tmp/oop.pipe

--//你可以發現seesion 2並沒有輸出.快取的作用.必須執行spool off,session 2 的tail -f才會輸出.

3.利用這個特性可以執行如下:

--//seesion 1:
spool /tmp/oop.pipe
select * from v$database
@ prxx
spool off

--//seesion 2:
$ tail -f /tmp/oop.pipe | grep SUPP
SUPPLEMENTAL_LOG_DATA_MIN     : YES
SUPPLEMENTAL_LOG_DATA_PK      : NO
SUPPLEMENTAL_LOG_DATA_UI      : NO
SUPPLEMENTAL_LOG_DATA_FK      : NO
SUPPLEMENTAL_LOG_DATA_ALL     : NO
SUPPLEMENTAL_LOG_DATA_PL      : NO

4.你也可以建立如下指令碼:
$ cat prp.sql
spool /tmp/oop.pipe
@ prxx
spool off

--//session 1先執行:
select * from v$database;

--//session 2執行:
$ tail -f /tmp/oop.pipe | grep SUPP

--//session 1執行:
@ prp

--//由於快取影響,最好執行2次.避免資訊混亂,你可以在session 2上先會幾個回車,再在session 1上執行.

$ tail -f /tmp/oop.pipe | egrep "_CHANGE|SUPP"
SUPPLEMENTAL_LOG_DATA_PL      : NO
MIN_REQUIRED_CAPTURE_CHANGE#  :


RESETLOGS_CHANGE#             : 925702
PRIOR_RESETLOGS_CHANGE#       : 1
CHECKPOINT_CHANGE#            : 45243913791
ARCHIVE_CHANGE#               : 45243779946
CONTROLFILE_CHANGE#           : 45243917934
ARCHIVELOG_CHANGE#            : 45243913791
SUPPLEMENTAL_LOG_DATA_MIN     : YES
SUPPLEMENTAL_LOG_DATA_PK      : NO
SUPPLEMENTAL_LOG_DATA_UI      : NO
SUPPLEMENTAL_LOG_DATA_FK      : NO
SUPPLEMENTAL_LOG_DATA_ALL     : NO
SUPPLEMENTAL_LOG_DATA_PL      : NO
MIN_REQUIRED_CAPTURE_CHANGE#  :

--//這樣就不需要鍛鍊眼力了.

5.繼續測試:
--//session 1:
SCOTT@book> set linesize 20000
SCOTT@book> set colsep |
SCOTT@book> spool /tmp/oop.pipe
SCOTT@book> @ desc v$database
SCOTT@book> spool off

--//session 2:
$ tail -f /tmp/oop.pipe | egrep "SUPP"
   30      SUPPLEMENTAL_LOG_DATA_MIN                VARCHAR2(8)
   31      SUPPLEMENTAL_LOG_DATA_PK                 VARCHAR2(3)
   32      SUPPLEMENTAL_LOG_DATA_UI                 VARCHAR2(3)
   40      SUPPLEMENTAL_LOG_DATA_FK                 VARCHAR2(3)
   41      SUPPLEMENTAL_LOG_DATA_ALL                VARCHAR2(3)
   51      SUPPLEMENTAL_LOG_DATA_PL                 VARCHAR2(3)
--//可以確定這些相關列在30,31,32,40,41,51.
--//sesson 2先執行:
$ tail -f /tmp/oop.pipe | cut -d"|" -f30,31,32,40,41,51

--//session 1:
SCOTT@book> spool /tmp/oop.pipe
SCOTT@book> select * from v$database;
SCOTT@book> spool off

--//sesson 2的輸出:
SCOTT@book> select * from v$database ;
SUPPLEME|SUP|SUP|SUP|SUP|SUP
--------|---|---|---|---|---
YES     |NO |NO |NO |NO |NO

--//你也可以將prp.sql指令碼修改如下,不過這樣必須先在session 1執行命令,這裡是select * from v$database;:
$ cat prp.sql
spool /tmp/oop.pipe
/
spool off

6.prxx指令碼如下:
--//前面的點可不是多餘的.如果windows下執行,必須修改_pr_tmpfile定義,以及host rm改為host del.

$ 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