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