Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using bind variables (in & out) with dynamic sql

Erik_NLJun 29 2012 — edited Jul 1 2012
I got a table that holds pl/sql code snippets to do validations on a set of data. what the code basically does is receiving a ID and returning a number of errors found.
To execute the code I use dynamic sql with two bind variables.

When the codes consists of a simpel query, it works like a charm, for example with this code:
BEGIN
   SELECT COUNT (1)
   INTO :1
   FROM articles atl
   WHERE ATL.CSE_ID = :2 AND cgp_id IS NULL;
END;
however when I get to some more complex validations that need to do calculations or execute multiple queries, I'm running into trouble.
I've boiled the problem down into this:
DECLARE
   counter   NUMBER;
   my_id     NUMBER := 61;
BEGIN
   EXECUTE IMMEDIATE ('
      declare 
         some_var number;
      begin
      
      select 1 into some_var from dual
      where :2 = 61; 
      
      :1 := :2;
      end;
')
      USING OUT counter, IN my_id;

   DBMS_OUTPUT.put_line (counter || '-' || my_id);
END;
this code doesn't really make any sense, but it's just to show you what the problem is. When I execute this code, I get the error
ORA-6537 OUT bind variable bound to an IN position

The error doesn't seem to make sense, :2 is the only IN bind variable, and it's only used in a where clause.
As soon as I remove that where clause , the code will work again (giving me 61-61, in case you liked to know).

Any idea whats going wrong? Am I just using the bind variables in a way you're not supposed to use them?

I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
This post has been answered by Billy Verreynne on Jun 29 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 29 2012
Added on Jun 29 2012
5 comments
1,449 views