步骤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:尝试在已使用的临时表上创建、修改或删除索引
查看这些错误是否预定义。但事实上它们不是。因此需要像下面这样定义:
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
create global temporary table ....
) 如果是,为什么要删除它?这是安装脚本的一部分吗?如果不是,也许一个全局临时表可以满足您的需求,而无需删除它。 - Shannon Severance