How to Dynamically Call PL/SQL Procedure in Oracle

Jun 4, 2009 / By Alex Gorbachev

Tags:

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.

13 Responses to “How to Dynamically Call PL/SQL Procedure in Oracle”

  • Matthew Watson says:

    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.

    http://en.wikipedia.org/wiki/Reflection_(computer_science)

    execute immediate is of course a fantastic option for hosing your your database :)

  • Rafa says:

    ok, you just made me fear the oracle power!

    Now I want to see a procedure that dynamically creates it self.

  • Tim Hall says:

    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…

  • Alex Gorbachev says:

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

  • LewisC says:

    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

  • joel garry says:

    it can be used to run anonymous PL/SQL blog

    As far as typos go, that was pretty entertaining. It could…

  • Alex Gorbachev says:

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

  • plsql says:

    Good discussion. One question is : what if the need to pass parameter to this dynamic procedure. Might be an issue here.

  • Alex Gorbachev says:

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

  • Alex Gorbachev says:

    @Oliver: re invoker vs definer rights – that’s what I was hinting at when used procedure to clean up. ;-)

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>