Skip to Main Content
  • Questions
  • How to EXECUTE IMMEDIATE "stored_procedure" using bind variables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Duke.

Asked: December 20, 2004 - 9:36 pm UTC

Last updated: February 12, 2005 - 8:27 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom-- Is it possible to invoke a stored procedures dynamically? Something like:

PROCEDURE dynamic_call ( p_proc_name VARCHAR2, p_x NUMBER, p_y NUMBER)
IS
BEGIN
EXECUTE IMMEDIATE 'SP_'||p_proc_name||' ( :1, :2 ) ' USING p_x, p_y;
END;

Thanks!

and Tom said...

you have to execute a block of code:


execute immediate 'begin sp_' || p_proc_name || '(:1,:2); end;' using p_x,p_y;


</code> http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/11_dynam.htm#8749 <code>

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 21, 2004 - 9:11 am UTC


IN & OUT

Duke Ganote, February 10, 2005 - 2:41 pm UTC

Tom-- Thank you!  Now that I've actually read the manual you referenced, it's obvious how to handle OUT parameters too:

SQL> ed
Wrote file afiedt.buf

  1  create procedure sp_test_dynamic_parms ( X IN NUMBER, y OUT NUMBER ) AS
  2           BEGIN
  3              y := X + 1;
  4*          END;
SQL> /

Procedure created.

SQL> DECLARE
  2    a number;
  3    b number := 1;
  4  BEGIN
  5    execute immediate
  6       'BEGIN sp_test_dynamic_parms ( :1, :2 ); END;'
  7                             USING IN b, OUT a;
  8    DBMS_OUTPUT.PUT_LINE('result = '||a);
  9  END;
 10  /
result = 2

PL/SQL procedure successfully completed.
 

extra credit

Duke Ganote, February 11, 2005 - 2:11 pm UTC

Of course, I'd be remiss if I didn't follow "Professor Tom"'s advice to TKPROF compare dynamic and static calls. I found these in the dynamic call portion:

declare a number; begin
for i in 1..100000 loop
execute immediate
'BEGIN sp_test_dynamic_parms ( :1, :2 ); END;'
USING IN i, OUT a;
DBMS_APPLICATION_INFO.SET_ACTION('@'||i);
end loop;
END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.05 0 0 0 0
Execute 1 23.84 35.68 0 2 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 23.85 35.74 0 2 0 1

BEGIN sp_test_dynamic_parms ( :1, :2 ); END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100000 11.25 16.26 0 0 0 0
Execute 100000 17.50 25.88 0 0 0 200000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 28.76 42.15 0 0 0 200000

>>>>>>> versus <<<<<<<<<

declare a number; begin
for i in 1..100000 loop
sp_test_dynamic_parms ( i, a );
DBMS_APPLICATION_INFO.SET_ACTION('@'||i);
end loop;
END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 2.95 3.74 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.96 3.74 0 0 0 1

I assume the summation of rounding errors explains the "greater" elapsed time reported for the 100000 calls to
BEGIN sp_test_dynamic_parms ( :1, :2 ); END;
than for the single anonymous block that made the calls.

Tom Kyte
February 12, 2005 - 8:27 am UTC

more than likely, timeing a fairly fast thing inside of a tight loop rather than timing the loop itself.

but you have, as I have many times, shown why you don't want to dynamically invoke a procedure unless you have to.



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library