dropping a global temporary table
Solution 1
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:
- ORA-00942: table or view does not exist
- ORA-14452: attempt to create, 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 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
Solution 2
-- First Truncate temporary table SQL> TRUNCATE TABLE test_temp1; -- Then Drop temporary table SQL> DROP TABLE test_temp1;
Kamran koupaee
Updated on January 03, 2020Comments
-
Kamran koupaee over 4 years
2 Separate questions.
I am using this script to drop a table [SOLVED]
BEGIN EXECUTE IMMEDIATE 'DROP TABLE_NAME'; DBMS_OUTPUT.PUT_LINE ('Global table TABLE_NAME Dropped'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Global table TABLE_NAME Doesn''t exist.'); END; /
Is there anyway I can differentiate if table "doesn't exist" or it is being used in some other sessions (in that case it would locked and couldn't be deleted). I am not sure if I can see that table exists in user_tables. I am not fully aware of permissions.
I have added this code now
WHEN OTHERS THEN i_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1, 64); if i_code = -942 THEN DBMS_OUTPUT.PUT_LINE ('TABLE_NAME doesn''t exist. Script will continue to create it'); ELSE DBMS_OUTPUT.PUT_LINE ('Error dropping temporary table. The error code is ' || i_code || '- ' || v_errm); END IF ;
2. I see . at the end of each procedure like this
END PROCEDURE_NAME; . / sho err;
I just don't understand why . is here. Is it syntax or what?