[20251123]建立完善bcaz.sql脚本.txt

$ cat bcaz.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
----------------------------------------------------------------------------------------------
--
-- Name:        bcaz.sql.sql
-- Purpose:     Display for binding variable types as well as for data information using regexp_like
--
-- Author:      lfree
-- Usage:
--     @ bcaz <sql_id> <column_name_regexp>
--
-----------------------------------------------------------------------------------------------
set verify off
prompt
prompt @bind_cap_awr sql_id [column]
prompt
column value_string format a50
column datatype_string format a15

set term off
col 2 new_value 2
select null "2" from dual where 1=2;
select decode('&2',null,'*','&2') "2" from dual;
set term on

break on snap_id on sql_id on was_captured on last_captured skip 1
select  replace(sql_text,chr(13),'') c200 from DBA_HIST_SQLTEXT where sql_id='&1' and rownum<=1;

SELECT snap_id
        ,INSTANCE_NUMBER
        ,sql_id
        ,was_captured
        ,last_captured
        ,name
        ,position
        ,max_length
        ,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 DBA_HIST_SQLBIND
   WHERE     sql_id = '&1'
         AND was_captured = 'YES'
         AND dup_position IS NULL
         AND regexp_like (lower(name) ,lower('&2'))
         --AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%')
ORDER BY snap_id
        ,last_captured
        ,was_captured
        ,position;

clear break