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_at_freelists.org
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
-----------------------------------------------------------------