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

--//以前写过bind_cap.sql脚本。用于查询抓取的绑定变量值，查询那些变量使用的是like表达式。一些查询不是很方便，建立一个新的
--//脚本，命名bcz.sql用来支持正则表达式，更加灵活一些。

--//同时建立软连接bc.sql,bca.sql，这样不用打太多字符。

$ ln -s bind_cap.sql bc.sql
$ ln -s bind_cap_awr.sql bca.sql

--//代码如下：
$ cat bcz.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
----------------------------------------------------------------------------------------------
--
-- Name:        bcz.sql
-- Purpose:     Display for binding variable types as well as for data information using regexp_like
--
-- Author:      lfree
-- Usage:
--     @ bcz <sql_id> <column_name_gexp>
--
-----------------------------------------------------------------------------------------------
set verify off
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 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
                , inst_id
    --        decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30
    FROM gv$sql_bind_capture
   WHERE     sql_id = '&1'
         AND was_captured = 'YES'
         AND DUP_POSITION IS NULL
         AND regexp_like (lower(name) ,lower('&2'))
--       ND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%')
ORDER BY child_number, inst_id,was_captured, position;

clear break

--//简单测试如下：
SCOTT@book01p> variable a1  number
SCOTT@book01p> variable a2 varchar2(14)
SCOTT@book01p> variable b3 varchar2(14)
SCOTT@book01p> exec :a1 := 20;
PL/SQL procedure successfully completed.

SCOTT@book01p> exec :a2 := '20';
PL/SQL procedure successfully completed.

SCOTT@book01p> exec :b3 := '20';
PL/SQL procedure successfully completed.

SCOTT@book01p> select * from dept where deptno = :a1 and dname = :a2 and loc = :b3;
no rows selected

SCOTT@book01p> @ sql_id a5h28ggbxa25b
-- SQL_ID = a5h28ggbxa25b come from shared pool
select * from dept where deptno = :a1 and dname = :a2 and loc = :b3;

SCOTT@book01p> @ bcz a5h28ggbxa25b ''
SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING INST_ID
------------- ------------ --- ----- -------- ---------- ------------------- --------------- ------------ -------
a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                 1
                           YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                 1
                           YES :B3          3         32 2025-11-23 15:43:45 VARCHAR2(32)    20                 1



SCOTT@book01p> @ bcz a5h28ggbxa25b a[1|2]
SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING  INST_ID
------------- ------------ --- ---- --------- ---------- ------------------- --------------- ------------ --------
a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                  1
                           YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1

SCOTT@book01p> @ bcz a5h28ggbxa25b a
SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING  INST_ID
------------- ------------ --- ---- --------- ---------- ------------------- --------------- ------------ --------
a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                  1
                           YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1


SCOTT@book01p> @ bcz a5h28ggbxa25b b|a
SQL_ID        CHILD_NUMBER WAS NAME  POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING  INST_ID
------------- ------------ --- ---- --------- ---------- ------------------- --------------- ------------ --------
a5h28ggbxa25b            0 YES :A1          1         22 2025-11-23 15:43:45 NUMBER          20                  1
                           YES :A2          2         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1
                           YES :B3          3         32 2025-11-23 15:43:45 VARCHAR2(32)    20                  1