步骤1.确定要捕获的错误:
如果表不存在:
SQL> drop table x;
drop table x
*
ERROR at line 1:
ORA-00942: table or view does not exist
如果表正在使用中:
SQL> create global temporary table t (data varchar2(4000));
Table created.
在另一个会话中使用该表。 (注意插入后没有提交或任何内容。)
SQL> insert into t values ('whatever');
1 row created.
回到第一个会话,尝试删除:
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
陷阱的两个错误:
ORA-00942:表或视图不存在
ORA-14452:尝试在已使用的临时表上创建,更改或删除索引
看看错误是否是predefined。他们不是。所以他们需要像这样定义:
create or replace procedure p as
table_or_view_not_exist exception;
pragma exception_init(table_or_view_not_exist, -942);
attempted_ddl_on_in_use_GTT exception;
pragma exception_init(attempted_ddl_on_in_use_GTT, -14452);
begin
execute immediate 'drop table t';
exception
when table_or_view_not_exist then
dbms_output.put_line('Table t did not exist at time of drop. Continuing....');
when attempted_ddl_on_in_use_GTT then
dbms_output.put_line('Help!!!! Someone is keeping from doing my job!');
dbms_output.put_line('Please rescue me');
raise;
end p;
结果,首先没有t:
SQL> drop table t;
Table dropped.
SQL> exec p;
Table t did not exist at time of drop. Continuing....
PL/SQL procedure successfully completed.
现在,使用t:
SQL> create global temporary table t (data varchar2(4000));
Table created.
在另一场会议中:
SQL> insert into t values (null);
1 row created.
然后在第一次会议中:
SQL> exec p;
Help!!!! Someone is keeping from doing my job!
Please rescue me
BEGIN p; END;
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at "SCHEMA_NAME.P", line 16
ORA-06512: at line 1