Use dbms_job.submit to call 'execute immediate'

  

SQL> create table myTable (
  2   tname varchar2(30),
  3   cname varchar2(30),
  4   changed date
  5  );

Table created.

SQL>
SQL> create or replace procedure RUN_DDL(m varchar2) is
  2   pragma autonomous_transaction;
  3   begin
  4   execute immediate m;
  5   end;
  6  /
SQL>
SQL> create or replace procedure ADD_COLUMN(p_table varchar2, p_column varchar2) is
  2   v number;
  3   j number;
  4   begin
  5       insert into myTable values (p_table, p_column, sysdate);
  6       dbms_job.submit(j, 'run_ddl(''alter table '||p_table||' add '||p_column||''');');
  7   end;
  8  /

Procedure created.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

Related examples in the same category

1.	Simple Procedure with SUBMIT
2.	Using DBMS_OUTPUT to See the Assigned Job Number
3.	Using the SUBMIT Procedure
4.	DBMS_JOB package.
5.	Remove a procedure from the queue after 5 executions.
6.	how to submit TempInsert as a job.
7.	Submit job to change password

Use dbms_job.submit to call 'execute immediate' : dbms_job « System Packages « Oracle PL / SQL

Related examples in the same category