Oracle 11g "Bind variable does not exist"

Question

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.

Maheswaran Ravisankar · Accepted Answer · 2014-10-21 17:16:04Z

5

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.

edited Oct 21, 2014 at 17:16

answered Oct 21, 2014 at 16:35

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bassman592 Over a year ago

Thank you. I made that change and now I get the error "ORA-01008: Not all variables bound".

Maheswaran Ravisankar Over a year ago

Can you paste the query you get in the output ?

bassman592 Over a year ago

This time there is no output.

Collectives™ on Stack Overflow

Oracle 11g "Bind variable does not exist"

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related