CODEWITHSUNDEEP
sqloracle-database
Ganesan VC
Ganesan VC

Reputation: 53

Getting error bind variable does not exist

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;
 /

Upvotes: 0

Views: 1934

Answers (3)

tmrozek
tmrozek

Reputation: 79

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.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

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;

Upvotes: 0

Matthew McPeak
Matthew McPeak

Reputation: 17924

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).

Upvotes: 1

Related Questions