Re: Script to overcome ORA-54 during DDL
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 25 Jun 2004 23:11:09 -0400
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
