pragma autonomous_transaction and exception : Autonomous_transaction « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Autonomous_transaction

pragma autonomous_transaction and exception

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

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  /

Procedure created.

SQL>
SQL> create or replace procedure ADD_COLUMN(p_table varchar2, p_column varchar2) is
  2   v number;
  3   begin
  4       insert into myTable values (p_table, p_column, sysdate);
  5       run_ddl('alter table '||p_table||' add '||p_column);
  6       v := 1/0;
  7   end;
  8  /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> drop table myTable;

Table dropped.

   
    
    
  

Related examples in the same category