[20221208]完善bind_cap.sql指令碼.txt

[20221208]完善bind_cap.sql指令碼.txt

--//以前寫的抓取繫結變數的指令碼,如果繫結變數型別是timestamp型別是儲存在v$sql_bind_capture.value_anydata中.
--//我以前的寫法如下,不合理多建立一個顯示欄位.
$ cat bind_cap.sql
set verify off
column value_string format a50
column datatype_string format a15
break on sql_id on child_number  skip 1
select  replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1;

SELECT sql_id,
       child_number,
       was_captured,
       name,
       position,
       max_length,
       last_captured,
       datatype_string,
       DECODE (
          datatype_string,
          'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'),
                           'yyyy/mm/dd hh24:mi:ss'),
          value_string)
          value_string,
          decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30
  FROM v$sql_bind_capture
 WHERE sql_id = '&1' and was_captured='YES' and  DUP_POSITION is null and lower(name) like lower('%'||nvl('&&2',name)||'%')
 order by child_number,was_captured,position;
clear break

--//改寫後程式碼如下:
$ cat bind_cap.sql
set verify off
column value_string format a50
column datatype_string format a15
break on sql_id on child_number  skip 1
select  replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1;

SELECT sql_id
        ,child_number
        ,was_captured
        ,name
        ,position
        ,max_length
        ,last_captured
        ,datatype_string
        ,DECODE
         (
            datatype_string
           ,'DATE', TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss')
           ,'TIMESTAMP', TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')
           ,value_string
         )
            value_string
    --        decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30
    FROM v$sql_bind_capture
   WHERE     sql_id = '&1'
         AND was_captured = 'YES'
         AND DUP_POSITION IS NULL
         AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%')
ORDER BY child_number, was_captured, position;

clear break