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