Oracle 11g“绑定变量不存在”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26491682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 11g "Bind variable does not exist"
提问by bassman592
I am getting an "ORA01006 Bind variable does not exist at line 15 "error in the following code:
我在以下代码中收到“第 15 行不存在 ORA01006 绑定变量”错误：
DECLARE
v_search_string varchar2(4000) := 'OK';
v_query_str VARCHAR2(4000);
match_count integer;
BEGIN
FOR t IN (SELECT owner,
table_name,
column_name
FROM all_tab_columns
WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') And TABLE_NAME = 'T1' And OWNER = 'O1')
LOOP
Begin
v_query_str := 'SELECT COUNT(*) FROM '|| t.table_name || ' WHERE ' || t.column_name || ' Like ''' || '%:1%' || '''';
dbms_output.put_line(v_query_str);
EXECUTE Immediate v_query_str
INTO match_count
USING v_search_string;
IF match_count >= 0 THEN
dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END;
END LOOP;
END;
I'm just trying to loop through all the character columns in the table and count how many values in each match the v_search_string value.
我只是想遍历表中的所有字符列并计算每个匹配 v_search_string 值的值。
The line "dbms_output.put_line(v_query_str);" prints one line: SELECT COUNT(*) FROM T1 WHERE Col1 Like '%:1%'
行“dbms_output.put_line(v_query_str);” 打印一行：SELECT COUNT(*) FROM T1 WHERE Col1 Like '%:1%'
There are 10 columns in the table that are the specified types.
表中有 10 列是指定类型。
There is obviously a bind variable there (%1), so I can't figure out what's going on.
那里显然有一个绑定变量 (%1)，所以我无法弄清楚发生了什么。
采纳答案by Maheswaran Ravisankar
Form the string like this below.
像下面这样形成字符串。
t.column_name || ' Like ''%''||:1||''%'''
Bind variable should not be included within single quotes, as it would be treated as a String literal instead. So when you used USINGit ended up with this excpetion.
绑定变量不应包含在单引号中，因为它会被视为字符串文字。因此，当您使用USING它时，最终会出现这种情况。
