Oracle PL/SQL dynamic code execution

Question

I want to execute dynamic code in an oracle pl/sql statement.

What works good is:

execute immediate 'begin :r := 3*7*(:aa); end;'  using in out myresvar,100;

Now I want to do it more generalized:

DECLARE

  -- arbitrary formula to evaluate
  formula VARCHAR2(100) := 'c+si/2.+mn/6.';

  -- string variable to keep pl/sql-code
  dynCall VARCHAR2(2046);

  -- variables to put into formula evaluation
  c   NUMBER := 0.02;
  si  NUMBER := 0.35;
  mn  NUMBER := 1.5;
  res NUMBER;
BEGIN

  dynCall := dynCall || 'DECLARE'       || chr(10);
  dynCall := dynCall || '  c   NUMBER;' || chr(10);
  dynCall := dynCall || '  si  NUMBER;' || chr(10);
  dynCall := dynCall || '  mn  NUMBER;' || chr(10);
  dynCall := dynCall || '  res NUMBER;' || chr(10);
  dynCall := dynCall || 'BEGIN'         || chr(10);

  dynCall := dynCall || '  c   := (:aa);' || chr(10);
  dynCall := dynCall || '  si  := (:bb);' || chr(10);
  dynCall := dynCall || '  mn  := (:cc);' || chr(10);

  dynCall := dynCall || '  :r := ' || formula || ';' || chr(10);

  dynCall := dynCall || 'END;'                    || chr(10);

  EXECUTE IMMEDIATE dynCall USING IN OUT c,si,mn,res;

END;

Unfortunately this causes an error like (in german):

... Error in transaction: ORA-06536: IN-Bind-Variable an OUT-Position gebunden ...

i.e. there seems to be a confusion with in and output valiables for the execute immediate statement.

Now I'm confused about what is the problem.

Can anyone help me?

Michael Hecht · Accepted Answer · 2013-08-14 16:15:49Z

1

OK, found the solution myself:

I just have to correct the execute line by:

EXECUTE IMMEDIATE dynCall USING c,si,mn,OUT res;

The web documentation is missing such examples (I didn't find anything).

answered Aug 14, 2013 at 16:15

Michael Hecht

2,2837 gold badges31 silver badges39 bronze badges

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

2 Comments

REW Over a year ago

Actually, this link (docstore.mik.ua/orelly/oracle/guide8i/ch04_04.htm) has examples of this. Basically the OUT designator is required in front of EACH of the out variables, and doesn't apply generically to the entire set.

user272735 Over a year ago

Each bind variable has a parameter mode (one of in, out or in out). The default parameter mode is in. See documentation of EXECUTE IMMEDIATE Statement.

Michael Hecht · Accepted Answer · 2013-08-16 07:06:04Z

Ok, it can be done even simpler, without line breaks and almost without variable bindings:

  dynCall := 'DECLARE'        || 
             '  c   NUMBER:=' || c  || ';' || 
             '  si  NUMBER:=' || si || ';' || 
             '  mn  NUMBER:=' || mn || ';' || 
             '  p   NUMBER:=' || p  || ';' || 
             '  s   NUMBER:=' || s  || ';' || 
             '  n   NUMBER:=' || n  || ';' || 
             '  al  NUMBER:=' || al || ';' || 
             '  cu  NUMBER:=' || cu || ';' || 
             '  ni  NUMBER:=' || ni || ';' ||
             '  mo  NUMBER:=' || mo || ';' || 
             '  cr  NUMBER:=' || cr || ';' || 
             '  nb  NUMBER:=' || nb || ';' || 
             '  v   NUMBER:=' || v  || ';' || 
             '  ti  NUMBER:=' || ti || ';' || 
             '  b   NUMBER:=' || b  || ';' || 
             '  ca  NUMBER:=' || ca || ';' || 
             'BEGIN'          ||
             '  :res := ' || formula || ';' ||
             'END;';

  EXECUTE IMMEDIATE dynCall USING OUT res;

Collectives™ on Stack Overflow

Oracle PL/SQL dynamic code execution

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related