Re: Script to overcome ORA-54 during DDL

On 06/25/2004 10:46:50 PM, Connor McDonald wrote:

> --   when others then raise;


Why did you comment that out? One would think that you'd like to know about 
errors
like ORA-0942? I would suggest even better code:
create or replace procedure do_ddl(m_sql varchar2)
as
 in_use exception ;
 deceive exception;
 pragma exception_init(in_use, -54);
 pragma exception_init(deceive,-7445);
begin
 while true loop
  begin
   execute immediate m_sql;
   exit;
  exception 
   when in_use then null;
   when others then raise deceive;
  end;
  dbms_lock.sleep(0.01);
 end loop;
end;
/


Here is a little evil test case for my suggestion:

  1  declare
  2  deceive exception;
  3  pragma exception_init(deceive,-7445);
  4  no_table exception;
  5  pragma exception_init(no_table,-942);
  6  sql_cmd varchar2(128):='create table a as select * from b';
  7  begin
  8  execute immediate sql_cmd;
  9  exception
 10  when no_table then raise deceive;
 11* end;
SQL> /
declare
*
ERROR at line 1:
ORA-07445: exception encountered: core dump [] [] [] [] [] []
ORA-06512: at line 10
ORA-00942: table or view does not exist
 
 
It also works with ORA-600. It's very good if you want your 
developer to have a heart attack.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: