CODEWITHSUNDEEP
oracle-databaseplsqlexecuteusing
Michael Hecht
Michael Hecht

Reputation: 2253

Oracle PL/SQL dynamic code execution

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?

Upvotes: 1

Views: 757

Answers (2)

Michael Hecht
Michael Hecht

Reputation: 2253

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;

Upvotes: 0

Michael Hecht
Michael Hecht

Reputation: 2253

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

Upvotes: 1

Related Questions