Getting error bind variable does not exist

Question

when i try to execute the following query i am getting the desired output but with error bind variable does not exist. could someone advise to fix this error

Error :

Error report -
ORA-01006: bind variable does not exist
ORA-06512: at line 25
01006. 00000 -  "bind variable does not exist"

Query :

SET SERVEROUTPUT ON

DECLARE

v_sql varchar2(5000);
v_sql2 varchar2(5000);
 v_prj_id varchar2(4000):='B00781728,B00781628,B00781611,A43670001';
v_event  varchar2(5000) := 'CORE_DTS_INTERNAL';

BEGIN

FOR i IN (SELECT trim(regexp_substr(v_prj_id, '[^,]+', 1, LEVEL)) l
         FROM dual 
         CONNECT BY LEVEL <= regexp_count(v_prj_id, ',') + 1 
) LOOP


   v_sql :=  v_sql || 'select '''|| i.l ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from ' || i.l || '.SI_Recipient WHERE EVENT = ''' ||V_EVENT ||'''   UNION ALL ' || chr(10);


END LOOP;

v_sql2 :=  RTRIM(v_sql, 'UNION ALL ' || chr(10) ) ;

 Dbms_Output.Put_Line (v_sql2);

 EXECUTE IMMEDIATE V_SQL2 USING V_EVENT; 



  END;
 /

Matthew McPeak · Accepted Answer · 2019-11-19 14:08:44Z

1

Change

v_sql :=  v_sql ... WHERE EVENT = ''' ||V_EVENT ||'''   UNION ALL ...

to

v_sql :=  v_sql ... WHERE EVENT = :v_event   UNION ALL ...

Bind variables appear in SQL queries with a colon prefix, like :v_event.

Using a bind variable in this manner is better than just removing USING V_EVENT from your EXECUTE IMMEDIATE. SQL statements that use bind variables are more scalable (fewer latches and less memory needed).

answered Nov 19, 2019 at 14:08

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

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

2 Comments

Popeye Over a year ago

And how many variables will be needed in the using clause? Have you seen that it is in loop?

Matthew McPeak Over a year ago

No, I hadn't noticed that. This is a bad pattern - one schema per project. Building dynamic queries for each set of projects of interest at a particular time is not scalable. A separate query for every call, depending on how often the process runs, will flood the library cache and result in excessive hard parsing. I may update my answer with a better pattern if I get time later (or you can). In the meantime, you're right: there is no convenient way to use bind variables with a query built in a loop.

tmrozek · Accepted Answer · 2019-11-19 19:21:32Z

You can remove using clause and it'll be working but it isn't a good pattern. Using bind variables is better choice especially for security reasons. You can just change your declaration of v_sql and leave rest of code as it is.

v_sql :=  v_sql || 'select '''|| i.l ||''' AS "PRJ_ID", EVENT, email,modified_by,modified from ' || i.l || '.SI_Recipient WHERE EVENT = :V_EVENT   UNION ALL ' || chr(10);

EDIT: As somebody has ponted out in comment to other answer my solution would only work without loop (or in one-time loop of course). Hard parsing is always a bad idea but in that it can't (without some writing a horrible code) be done in other way. My solution wouldn't work because you need to pass value for every bind regardless of bind's name when excuting immediate sql code.

Popeye · Accepted Answer · 2019-11-19 14:37:49Z

0

Just remove using clause from execute immediate as you are not using bind variable(:1) in your query which you were previously using in your query:

EXECUTE IMMEDIATE V_SQL2; --USING V_EVENT;

edited Nov 19, 2019 at 14:37

user142162

answered Nov 19, 2019 at 13:40

Popeye

36k4 gold badges12 silver badges31 bronze badges

1 Comment

Popeye Over a year ago

You are using loop and as per your previous question which I answered, using clause will require 4 times same variable.

Collectives™ on Stack Overflow

Getting error bind variable does not exist

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related