Just got an interesting note on Twitter that you can’t call a stored procedure dynamically in Oracle from a PL/SQL block like passing the procedure name in a variable.
Well, yes we can!
And the answer is EXECUTE IMMEDIATE — it can be used to run anonymous PL/SQL blog and not just a SQL statement. However, you will want to think many many times before doing so… if you love your data.
Let’s create the test procedures:
SQL> create or replace procedure bingo as
2 begin
3 dbms_output.put_line('Bingo!');
4 end;
5 /
Procedure created.
SQL> create or replace procedure bambam as
2 begin
3 dbms_output.put_line('BAM BAM!');
4 end;
5 /
Procedure created.
Now let’s create a wrapper that we will call passing a procedure name:
SQL> create or replace procedure call_dynamic(proc_name IN varchar2) as 2 begin 3 execute immediate 'begin ' || proc_name || '; end;'; 4 end; 5 / Procedure created.
Now let’s enable server output and try to call our procedures dynamically
SQL> set serverout on
SQL> exec call_dynamic('bingo');
Bingo!
PL/SQL procedure successfully completed.
SQL> exec call_dynamic('bambam');
BAM BAM!
PL/SQL procedure successfully completed.
Now, what was that we wanted to think about? Ah here… Let’s create a table:
SQL> create table important_data (data varchar2(1000));
Table created.
SQL> insert into important_data values ('Oracle rocks!');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from important_data;
DATA
-----------------------
Oracle rocks!
So what can someone do with such procedure… perhaps, something like this?
SQL> exec call_dynamic('delete from important_data; commit');
PL/SQL procedure successfully completed.
SQL> select * from important_data;
no rows selected
Oups… or maybe something like this?
SQL> exec call_dynamic('execute immediate ''drop table important_data''');
PL/SQL procedure successfully completed.
SQL> select * from important_data;
select * from important_data
*
ERROR at line 1:
ORA-00942: table or view does not exist
You’ve got an idea. :)
OK. Let’s finally clean up:
SQL> exec call_dynamic('execute immediate ''drop procedure bingo''')
PL/SQL procedure successfully completed.
SQL> exec call_dynamic('execute immediate ''drop procedure bambam''')
PL/SQL procedure successfully completed.
And final clean up needs to be smarter as this will hang blocking itself:
SQL> exec call_dynamic('execute immediate ''drop procedure call_dynamic''')
^CBEGIN call_dynamic('execute immediate ''drop procedure call_dynamic'''); END;
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 1
ORA-06512: at "SYS.CALL_DYNAMIC", line 3
ORA-06512: at line 1
But doable:
SQL> exec call_dynamic('declare a number; begin dbms_job.submit(a,''begin execute immediate ''''drop procedure call_dynamic''''; end;''); commit; end')
PL/SQL procedure successfully completed.
SQL> exec call_dynamic('null');
BEGIN call_dynamic('null'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CALL_DYNAMIC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Anyway, the real requirements were more complicated. Granted I don’t know all the details but I strongly suspect that there would be a better solution than calling a PL/SQL procedure dynamically.
16 Comments. Leave new
Thats kind of what I was after, but specifically dynamically calling a private procedure from within a package. Unfortunately I haven’t been able to find a workable solution to my problem.
https://en.wikipedia.org/wiki/Reflection_(computer_science)
execute immediate is of course a fantastic option for hosing your your database :)
ok, you just made me fear the oracle power!
Now I want to see a procedure that dynamically creates it self.
Hi.
Without wanting to sound obvious, the big issue with dynamically calling a procedure is what happens if the procedure is not there? :)
People really need to think carefully about whether they actually need dynamic SQL in their production code. It’s a very good way of introducing runtime errors into your code. There is a warm and fuzzy feeling about no compile-time errors/warnings, but this means nothing if you are using dynamic SQL.
Obvious warning over… :)
Regarding calling a private procedure, that is what invoker rights is for. :)
Cheers
Tim…
I think Reflection approach is a sure way to make sure your code won’t be understood by future developers and make sure there are so many bugs that there is no way to fix them over the life-time of the program. :)
I think I have to go with Alex on this one. There is probably a better approach than dynamic calls and definitely a better approach than reflection. ugh. That just sounds like a nightmare in the long run.
LewisC
it can be used to run anonymous PL/SQL blog
As far as typos go, that was pretty entertaining. It could…
Thanks for the feedback guys. I didn’t touch on twists with definer and invoker privileges but that’s another topic.
joel, LOL — that’s funny indeed. I won’t correct it and leave it as is — it’s too funny. :)
[…] Here on the Pythian blog, Alex Gorbachev showed how to dynamically call a PL/SQL procedure. […]
Good discussion. One question is : what if the need to pass parameter to this dynamic procedure. Might be an issue here.
@plsql: Can you clarify potential issues? I don’t see any additional complications. I showed how to use that procedure. If you want, you can even make the wrapper more fine-tuned with additional parameters to be passed.
There is an other issue: user rights.
PL/SQL procedures run with the rights of the user which own them instead of the rights of the user who call them.
This means that the call_dynamically() presented here may also be a way for a user to escape a sandbox to get higher privileges.
@plsql: you can bind variables in the EXECUTE IMMEDIATE call. See the reference documentation about EXECUTE IMMEDIATE.
@Oliver: re invoker vs definer rights – that’s what I was hinting at when used procedure to clean up. ;-)
Lots of comments that this should never be run in production.
Never covers a lot of territory, and there are surely exceptions.
For instance, consider the following scenario:
– Oracle Queues are used for different stages of processing incoming data
– There is a different procedure in a package for each business rule handled by a queue.
– The process is data driven. A table is used to store the ‘chain of command’.
The processes to run are fetched from the table, and run in that order.
The ‘call_dynamic()’ procedure is locked down in the sense that the only code to be run
is procedures in the same package.
The procedure name is passed, and verified with dbms_utility.name_resolve
The call_dynamic() procedure could even consider that driving table as a whitelist and verify
that the procedure exists in the package and in the table.
Many Languages have facilities to elegantly run such dynamic chains of code.
PL/SQL unfortunately, does not.
Almost 10 years later you got time to come back to it? :)
Your TOREAD list must be pretty long! :)
Wonderful explanation for a procedure, can you please give an example in which a function is dynamically calling a function. I am very confused and need some urgent help.