Oracle 11g "Bind variable does not exist"
24,213
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 USING
it ended up with this excpetion.
Author by
bassman592
Updated on July 09, 2022Comments
-
bassman592 almost 2 years
I am getting an "ORA01006 Bind variable does not exist at line 15 "error in the following code:
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.
The line "dbms_output.put_line(v_query_str);" prints one line: SELECT COUNT(*) FROM T1 WHERE Col1 Like '%:1%'
There are 10 columns in the table that are the specified types.
There is obviously a bind variable there (%1), so I can't figure out what's going on.