oracle 删除中文表,oracle - 删除全局临时表 - SO中文参考 - www.soinside.com

步骤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