[20221010]完善descz.sql脚本.txt

[20221010]完善descz.sql脚本.txt --//前一阵子执行descz遇到的问题,数据类型NVARCHAR2的不显示Low_value,High_value. --//例子如下: > @ descz lis.COM_GRIDSETTING "column_name in ('CLIENT_ID','CLASS_NAME','GRID_NAME')" eXtended describe of lis.COM_GRIDSETTING DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner Table_Name      SAMPLE_SIZE LAST_ANALYZED       Col# Column Name Null?      Type           NUM_DISTINCT      Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- --------------- ----------- ------------------- ---- ----------- ---------- -------------- ------------ ------------ --------- --------- ----------- --------- ---------- LIS   COM_GRIDSETTING       58684 2022-10-09 14:10:26    2 GRID_NAME   NOT NULL   NVARCHAR2(800)           17 .00000852021         0 FREQUENCY          17                             58684 2022-10-09 14:10:26    3 CLASS_NAME  NOT NULL   NVARCHAR2(800)           48 .00000852021         0 FREQUENCY          48                              5461 2022-10-09 14:10:26    7 CLIENT_ID   NOT NULL   NUMBER(10,0)            486 .00186200000         0 HYBRID            254 1         25113 --//查询NVARCHAR2类型时,没有显示Low_value,High_value,修改完善一下. --//没有包括timestamp类型,顺便加入看看.注意data_type中timestamp是以timestamp(N)格式出现. $ cat descz.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL desc_column_id      HEAD "Col#" FOR A4 COL desc_column_name    HEAD "Column Name" FOR A20 COL desc_data_type      HEAD "Type" FOR A20 WORD_WRAP COL desc_nullable       HEAD "Null?" FOR A10 COL desc_density        HEAD "Density" FOR 9.99999999999 COL desc_owner          HEAD Owner  FOR A10 COL desc_table_name     HEAD Table_Name    FOR A20 COL trans_low           HEAD "Low_value" FOR A40 COL trans_high          HEAD "High_value" FOR A40 prompt eXtended describe of &1 PROMPT PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. PROMPT INPUT   OWNER.TABLE_NAME  <filters> PROMPT SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" PROMPT IF NOT INPUT <filters> ,USE "1=1" . PROMPT break on desc_owner on desc_table_name skip 1 SELECT   owner       desc_owner,   table_name  desc_table_name,   sample_size ,   last_analyzed,         CASE WHEN hidden_column = 'YES' THEN 'H' ELSE ' ' END||         LPAD(column_id,3)       desc_column_id,         column_name     desc_column_name,         CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE NULL END AS desc_nullable,         data_type||CASE --      WHEN data_type = 'NUMBER' THEN '('||data_precision||CASE WHEN data_scale = 0 THEN NULL ELSE ','||data_scale END||')'         WHEN data_type = 'NUMBER' THEN '('||data_precision||','||data_scale||')'         ELSE '('||data_length||')'         END AS desc_data_type, --      data_default,         num_distinct,         density             desc_density,         num_nulls,   CASE WHEN histogram = 'NONE'  THEN null ELSE histogram END histogram,         num_buckets,     CASE        WHEN data_type IN ('CHAR', 'VARCHAR2')        THEN           UTL_RAW.cast_to_varchar2 (low_value)       WHEN data_type IN ('NVARCHAR2')        THEN           to_char(UTL_RAW.cast_to_nvarchar2 (low_value))        WHEN data_type = 'NUMBER'        THEN           TO_CHAR (UTL_RAW.cast_to_number (low_value))        WHEN data_type in ( 'DATE') or data_type like 'TIMESTAMP%'        THEN           RTRIM (                 LTRIM (                    TO_CHAR (                           100                         * (  TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')                            - 100)                       + (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100),                       '0000'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX'),                             '00'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX'),                             '00'))              || ' '              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1,                       '00')))     END            trans_low,     CASE        WHEN data_type IN ('CHAR', 'VARCHAR2')        THEN           UTL_RAW.cast_to_varchar2 (high_value)        WHEN data_type IN ('NVARCHAR2')        THEN           to_char(UTL_RAW.cast_to_nvarchar2 (high_value))        WHEN data_type = 'NUMBER'        THEN           TO_CHAR (UTL_RAW.cast_to_number (high_value))        WHEN data_type in ( 'DATE') or data_type like 'TIMESTAMP%'        THEN           RTRIM (                 LTRIM (                    TO_CHAR (                           100                         * (  TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')                            - 100)                       + (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100),                       '0000'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX'),                             '00'))              || '-'              || LTRIM (                    TO_CHAR (TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX'),                             '00'))              || ' '              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1,                       '00'))              || ':'              || LTRIM (                    TO_CHAR (                       TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1,                       '00')))     END        trans_high         --,'--' desc_succeeded FROM         dba_tab_cols WHERE         upper(table_name) LIKE                                 upper(CASE                                         WHEN INSTR('&1','.') > 0 THEN                                             SUBSTR('&1',INSTR('&1','.')+1)                                         ELSE                                             '&1'                                         END                                      ) AND     owner LIKE                 CASE WHEN INSTR('&1','.') > 0 THEN                         UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))                 ELSE                         user                 END --AND column_name = decode('&&2','',column_name,upper('&&2')) AND ( &&2 ) ORDER BY   owner,   table_name,         column_id / --//注:必须使用to_char转换一下,因为UTL_RAW.cast_to_nvarchar2返回类型是nvarchar2类型,前后不一致. SYS@192.168.100.235:1521/orcl> @ descz lis.COM_GRIDSETTING "column_name in ('CLIENT_ID','CLASS_NAME','GRID_NAME')" eXtended describe of lis.COM_GRIDSETTING DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER.TABLE_NAME  <filters> SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" IF NOT INPUT <filters> ,USE "1=1" . Owner      Table_Name           SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value        High_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- -------------- -------------- LIS        COM_GRIDSETTING            58684 2022-10-09 14:10:26    2 GRID_NAME            NOT NULL   NVARCHAR2(800)                 17   .00000852021          0 FREQUENCY                17 BisOutGridView layoutControl2                                       58684 2022-10-09 14:10:26    3 CLASS_NAME           NOT NULL   NVARCHAR2(800)                 48   .00000852021          0 FREQUENCY                48 AuditFrm        TestingFrm                                        5461 2022-10-09 14:10:26    7 CLIENT_ID            NOT NULL   NUMBER(10,0)                  486   .00186200000          0 HYBRID                  254 1        25113 --//OK,现在nvarchar2类型的Low_value,High_value有显示了. --//测试timestamp类型: SCOTT@test01p> create table empx as select * from emp; Table created. SCOTT@test01p> alter table empx modify(hiredate timestamp(6)); Table altered. SCOTT@test01p> update empx set hiredate=systimestamp-50*365  where empno=7369; 1 row updated. SCOTT@test01p> update empx set hiredate=systimestamp  where empno=7934; 1 row updated. SCOTT@test01p> commit; Commit complete. SCOTT@test01p> @ tpt/gts empx Gather Table Statistics for table empx... PL/SQL procedure successfully completed. SCOTT@test01p> select min(hiredate),max(hiredate) from empx; MIN(HIREDATE)              MAX(HIREDATE) -------------------------- -------------------------- 1972-10-22 20:39:27.000000 2022-10-10 20:35:42.536000 SCOTT@test01p> @ tpt/gts empx Gather Table Statistics for table empx... PL/SQL procedure successfully completed. SCOTT@test01p> @ descz empx column_name='HIREDATE' SCOTT@test01p> @ prxx ============================== DESC_OWNER                    : SCOTT DESC_TABLE_NAME               : EMPX SAMPLE_SIZE                   : 14 LAST_ANALYZED                 : 2022-10-10 20:40:34 DESC_COLUMN_ID                :    5 DESC_COLUMN_NAME              : HIREDATE DESC_NULLABLE                 : DESC_DATA_TYPE                : TIMESTAMP(6)(11) NUM_DISTINCT                  : 13 DESC_DENSITY                  : .0769230769230769 NUM_NULLS                     : 0 HISTOGRAM                     : NUM_BUCKETS                   : 1 TRANS_LOW                     : 1972-10-22 20:39:27 TRANS_HIGH                    : 2022-10-10 20:35:42 PL/SQL procedure successfully completed. --//丢失秒后面的数值. SELECT table_name,low_value,high_value   FROM dba_tab_cols  WHERE owner = 'SCOTT' AND table_name like  'EMP%' and column_name ='HIREDATE'; TABLE_NAME LOW_VALUE      HIGH_VALUE ---------- -------------- ------------------------------ EMP        77B40C11010101 77BB0517010101 EMPX       77AC0A1615281C 787A0A0A15242B1FF2B600 SCOTT@test01p> @ tpt/calc x1FF2B600  + 0              DEC      HEX ---------------- -------- 536000000.000000 1FF2B600 --//可以看出后面的536000000正好与select min(hiredate),max(hiredate) from empx;输出的最大值秒后面的值对上. --//再次修改脚本如下: /* Formatted on 2022/10/10 21:04:36 (QP5 v5.227.12220.39754) */ -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL desc_column_id   HEAD "Col#" FOR A4 COL desc_column_name HEAD "Column Name" FOR A20 COL desc_data_type   HEAD "Type" FOR A20 WORD_WRAP COL desc_nullable    HEAD "Null?" FOR A10 COL desc_density     HEAD "Density" FOR 9.99999999999 COL desc_owner       HEAD Owner         FOR A10 COL desc_table_name  HEAD Table_Name    FOR A20 COL trans_low        HEAD "Low_value" FOR A40 COL trans_high       HEAD "High_value" FOR A40 PROMPT eXtended describe of &1 PROMPT PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. PROMPT INPUT   OWNER.TABLE_NAME  <filters> PROMPT SAMPLE  : @ TAB_LH TABLE_NAME "column_id between 3 and 5" PROMPT IF NOT INPUT <filters> ,USE "1=1" . PROMPT BREAK ON desc_owner ON desc_table_name SKIP 1   SELECT owner desc_owner         ,table_name desc_table_name         ,sample_size         ,last_analyzed         ,   CASE WHEN hidden_column = 'YES' THEN 'H' ELSE ' ' END          || LPAD (column_id, 3)             desc_column_id         ,column_name desc_column_name         ,CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE NULL END             AS desc_nullable         ,   data_type          || CASE                -- WHEN data_type = 'NUMBER' THEN '('||data_precision||CASE WHEN data_scale = 0 THEN NULL ELSE ','||data_scale END||')'                WHEN data_type = 'NUMBER'                THEN                   '(' || data_precision || ',' || data_scale || ')'                ELSE                   '(' || data_length || ')'             END             AS desc_data_type         ,                                                --      data_default,          num_distinct         ,density desc_density         ,num_nulls         ,CASE WHEN histogram = 'NONE' THEN NULL ELSE histogram END histogram         ,num_buckets         ,CASE             WHEN data_type IN ('CHAR', 'VARCHAR2')             THEN                UTL_RAW.cast_to_varchar2 (low_value)             WHEN data_type IN ('NVARCHAR2')             THEN                TO_CHAR (UTL_RAW.cast_to_nvarchar2 (low_value))             WHEN data_type = 'NUMBER'             THEN                TO_CHAR (UTL_RAW.cast_to_number (low_value))             WHEN data_type IN ('DATE')             THEN                RTRIM                (                      LTRIM                      (                         TO_CHAR                         (                                100                              * (  TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')                                 - 100)                            + (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100)                           ,'0000'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX')                           ,'00'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX')                           ,'00'                         )                      )                   || ' '                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1                           ,'00'                         )                      )                )             WHEN data_type LIKE 'TIMESTAMP%'             THEN                RTRIM                (                      LTRIM                      (                         TO_CHAR                         (                                100                              * (  TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')                                 - 100)                            + (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100)                           ,'0000'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX')                           ,'00'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX')                           ,'00'                         )                      )                   || ' '                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1                           ,'00'                         )                      )                   || '.'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (low_value, 15), 'XXXXXXXXX')                           ,'000000000'                         )                      )                )          END             trans_low         ,CASE             WHEN data_type IN ('CHAR', 'VARCHAR2')             THEN                UTL_RAW.cast_to_varchar2 (high_value)             WHEN data_type IN ('NVARCHAR2')             THEN                TO_CHAR (UTL_RAW.cast_to_nvarchar2 (high_value))             WHEN data_type = 'NUMBER'             THEN                TO_CHAR (UTL_RAW.cast_to_number (high_value))             WHEN data_type IN ('DATE')             THEN                RTRIM                (                      LTRIM                      (                         TO_CHAR                         (                                100                              * (  TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')                                 - 100)                            + (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100)                           ,'0000'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX')                           ,'00'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX')                           ,'00'                         )                      )                   || ' '                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1                           ,'00'                         )                      )                )             WHEN data_type LIKE 'TIMESTAMP%'             THEN                RTRIM                (                      LTRIM                      (                         TO_CHAR                         (                                100                              * (  TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')                                 - 100)                            + (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100)                           ,'0000'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX')                           ,'00'                         )                      )                   || '-'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX')                           ,'00'                         )                      )                   || ' '                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1                           ,'00'                         )                      )                   || ':'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1                           ,'00'                         )                      )                   || '.'                   || LTRIM                      (                         TO_CHAR                         (                            TO_NUMBER (SUBSTR (high_value, 15), 'XXXXXXXXX')                           ,'000000000'                         )                      )                )          END             trans_high     --,'--' desc_succeeded     FROM dba_tab_cols    WHERE     UPPER (table_name) LIKE                 UPPER                 (                    CASE                       WHEN INSTR ('&1', '.') > 0                       THEN                          SUBSTR ('&1', INSTR ('&1', '.') + 1)                       ELSE                          '&1'                    END                 )          AND owner LIKE                 CASE                    WHEN INSTR ('&1', '.') > 0                    THEN                       UPPER (SUBSTR ('&1', 1, INSTR ('&1', '.') - 1))                    ELSE                       USER                 END          --AND column_name = decode('&&2','',column_name,upper('&&2'))          AND (&&2) ORDER BY owner, table_name, column_id / SCOTT@test01p> @ descz empx column_name='HIREDATE' SCOTT@test01p> @ prxx ============================== DESC_OWNER                    : SCOTT DESC_TABLE_NAME               : EMPX SAMPLE_SIZE                   : 14 LAST_ANALYZED                 : 2022-10-10 20:40:34 DESC_COLUMN_ID                :    5 DESC_COLUMN_NAME              : HIREDATE DESC_NULLABLE                 : DESC_DATA_TYPE                : TIMESTAMP(6)(11) NUM_DISTINCT                  : 13 DESC_DENSITY                  : .0769230769230769 NUM_NULLS                     : 0 HISTOGRAM                     : NUM_BUCKETS                   : 1 TRANS_LOW                     : 1972-10-22 20:39:27. TRANS_HIGH                    : 2022-10-10 20:35:42.536000000 PL/SQL procedure successfully completed. SCOTT@test01p> @ desczz emp column_name='HIREDATE' SCOTT@test01p> @ prxx ============================== DESC_OWNER                    : SCOTT DESC_TABLE_NAME               : EMP SAMPLE_SIZE                   : 14 LAST_ANALYZED                 : 2018-10-07 19:34:52 DESC_COLUMN_ID                :    5 DESC_COLUMN_NAME              : HIREDATE DESC_NULLABLE                 : DESC_DATA_TYPE                : DATE(7) NUM_DISTINCT                  : 13 DESC_DENSITY                  : .0769230769230769 NUM_NULLS                     : 0 HISTOGRAM                     : NUM_BUCKETS                   : 1 TRANS_LOW                     : 1980-12-17 00:00:00 TRANS_HIGH                    : 1987-05-23 00:00:00 PL/SQL procedure successfully completed.