[20180628]顯示bbed x命令格式.txt

[20180628]顯示bbed x命令格式.txt

--//測試經常使用bbed x命令顯示資料,裡面的格式每次輸入必須顯示錶結構才方便輸入.
--//如果欄位很多非常麻煩,寫一個指令碼完成這個工作.

--//這個是我以前寫的,12c已經淘汰wmsys.wm_concat,要使用listagg代替.
prompt
PROMPT DISPLAY BBED EXAMINE(X) FORMAT
prompt

SELECT REPLACE (wmsys.wm_concat (c1), ',') c80
  FROM (  SELECT data_type
                ,column_name
                ,DECODE
                 (
                    data_type
                   ,'NUMBER', 'n'
                   ,'CHAR', 'c'
                   ,'VARCHAR', 'c'
                   ,'VARCHAR2', 'c'
                   ,'DATE', 't'
                   ,' '
                 )
                    c1
            FROM dba_tab_cols
           WHERE owner = UPPER(nvl('&1',user)) AND TABLE_NAME = UPPER( '&2')
        ORDER BY column_id);


---//改寫如下,做一個記錄:
prompt
PROMPT DISPLAY BBED EXAMINE(X) FORMAT
prompt

SELECT REPLACE (LISTAGG (c1, ',') WITHIN GROUP (ORDER BY column_id), ',') c80
  FROM (  SELECT data_type
                ,column_id
                ,column_name
                ,DECODE
                 (
                    data_type
                   ,'NUMBER', 'n'
                   ,'CHAR', 'c'
                   ,'VARCHAR', 'c'
                   ,'VARCHAR2', 'c'
                   ,'DATE', 't'
                   ,' '
                 )
                    c1
            FROM dba_tab_cols
           WHERE     owner = UPPER (NVL ('&1', USER))
                 AND TABLE_NAME = UPPER ('&2')
                 AND hidden_column = 'NO'
        ORDER BY column_id);


SCOTT@test01p> @ bbedcol12 '' emp
DISPLAY BBED EXAMINE(X) FORMAT
C80
----------
nccntnnn

SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> select rowid,emp.* from emp where rownum=1;
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWh/AAJAAAACVAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:01        800                    20

SCOTT@test01p> @ rowid AAAWh/AAJAAAACVAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     92287          9        149          0  0x2400095           9,149                alter system dump datafile 9 block 149 ;


D:\tools\bbed>echo x /rnccntnnn dba 9,150 *kdbr[0] | bbed1
echo x /rnccntnnn dba 9,150 *kdbr[0] | bbed1
BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 28 20:56:28 2018
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************

BBED> set count 8192
        COUNT           8192

BBED> set width 160
        WIDTH           160

BBED> rowdata[570]                                @8150
------------
flag@8150: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8151: 0x01
cols@8152:    8

col    0[3] @8153: 7369
col    1[5] @8157: SMITH
col    2[5] @8163: CLERK
col    3[3] @8169: 7902
col    4[7] @8173: 1980-12-17 00:00:01
col    5[2] @8181: 800
col    6[0] @8184: *NULL*
col    7[2] @8185: 20