如何提取sql语句中绑定变量的值?
更新时间： 2016-09-17 00:18
我们在处理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中,就可以正常执行了.
原文地址：重庆思庄Oracle论坛《如何提取sql语句中绑定变量的值?》http://bbs.cqsztech.com/forum.php?mod=viewthread&tid=17100
原文地址：重庆思庄Oracle论坛《如何提取sql语句中绑定变量的值?》http://bbs.cqsztech.com/forum.php?mod=viewthread&tid=17100
