如何提取sql语句中绑定变量的值?

郑全

我们在处理sql语句性能问题时,很多时候,提取到的sql语句带了绑定变量的,不能直接拿来运行,如下的sql语句,直接执行,会报变量没有赋值之类的错误.

SELECT A.RECIPE_ID
FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 )) B
WHERE A.RECIPE_ID = B.COLUMN_VALUE
       OR A.PARENTID = B.COLUMN_VALUE
ORA-01008: 并非所有变量都绑定.

那么这个B1 具体是什么值呢?

马上查询v$sqlarea中的sql_id

SQL>  SELECT sql_id, child_number , sql_text FROM v$sql WHERE  upper(sql_text) Like '%SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 )) B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE%'
  2  ;
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
cn2c9mf5f9xda            0 SELECT sql_id, child_number , sql_text FROM v$sql WHERE  upper(sql_text) Like '%
07zdmp13w1zw6            0 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6            1 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6            2 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6            3 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
3akum7jd5b8j5            0   SELECT sql_id, child_number , sql_text FROM v$sql WHERE  upper(sql_text) Like
bjvppgp4jrfh8            0  SELECT sql_id, child_number , sql_text FROM v$sql WHERE  upper(sql_text) Like '
2na7kxsh0msa6            0 select sql_id, address, hash_value, executions, loads, parse_calls, invalidation
8 rows selected

通过以下sql语句察看
  SELECT name ,position,datatype_string,was_captured,value_string
    FROM   v$sql_bind_capture
    WHERE  sql_id = '&SQLID';

SQL> SELECT name ,position,datatype_string,was_captured,value_string
  2      FROM   v$sql_bind_capture
  3      WHERE  sql_id = '&SQLID';
输入: 07zdmp13w1zw6
SQL>
NAME                                                           POSITION DATATYPE_STRING                WAS_CAPTURED VALUE_STRING
------------------------------------------------------------ ---------- ------------------------------ ------------ --------------------------------------------------------------------------------
:B1                                                                   1 VARCHAR2(2000)                 NO           
:B1                                                                   1 VARCHAR2(2000)                 NO           
:B1                                                                   1 VARCHAR2(128)                  NO           
:B1                                                                   1 VARCHAR2(128)                  NO

这样,我们就已经找到:B1的值,代入原sql中,就可以正常执行了.