dropping a global temporary table

-- First Truncate temporary tableSQL> TRUNCATE TABLE test_temp1;-- Then Drop temporary tableSQL> DROP TABLE test_temp1;

Step 1. Figure out which errors you want to trap:

If the table does not exist:

SQL> drop table x;drop table x           *ERROR at line 1:ORA-00942: table or view does not exist

If the table is in use:

SQL> create global temporary table t (data varchar2(4000));Table created.

Use the table in another session. (Notice no commit or anything after the insert.)

SQL> insert into t values ('whatever');1 row created.

Back in the first session, attempt to drop:

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

So the two errors to trap:

1. ORA-00942: table or view does not exist
2. ORA-14452: attempt tocreate, alter or drop an index on temporary table already in use

See if the errors are predefined. They aren't. So they need to be defined like so:

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;

And results, first without 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.

And now, with t in use:

SQL> create global temporary table t (data varchar2(4000));Table created.

In another session:

SQL> insert into t values (null);1 row created.

And then in the first session:

SQL> exec p;Help!!!! Someone is keeping from doing my job!Please rescue meBEGIN p; END;*ERROR at line 1:ORA-14452: attempt to create, alter or drop an index on temporary table already in useORA-06512: at "SCHEMA_NAME.P", line 16ORA-06512: at line 1

yes - the engine will throw different exceptions for different conditions.

you will change this part to catch the exception and do something different

  EXCEPTION      WHEN OTHERS THEN

here is a reference

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm