How to Dynamically Call PL/SQL Procedure in Oracle

Dynamic procedure calls in Oracle PL/SQL

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.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?