Script to overcome ORA-54 during DDL 2004-06-25 - By Mladen Gogala
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 (See ORA-0942.ora-code.com)? 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 (See ORA-07445.ora-code.com): exception encountered: core dump [] [] [] [] [] []
ORA-06512 (See ORA-06512.ora-code.com): at line 10
ORA-00942 (See ORA-00942.ora-code.com): table or view does not exist
It also works with ORA-600 (See ORA-600.ora-code.com). 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
