dropping a global temporary table

Question

2 Separate questions.

1. 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?

Answer 1

-- First Truncate temporary table
SQL> TRUNCATE TABLE test_temp1;

-- Then Drop temporary table
SQL> DROP TABLE test_temp1;

Answer 2

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

Answer 3

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current connection.

These tables do not reside in the system catalogs and are not persistent.

Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection.

When the connection closes, the rows of the table are deleted, and the in-memory description of the temporary table is dropped.

For your reference http://docs.oracle.com/javadb/10.6.2.1/ref/rrefdeclaretemptable.html

Answer 4

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

Answer 5

1. Down the apache server by running below in putty cd $ADMIN_SCRIPTS_HOME ./adstpall.sh
2. Drop the Global temporary tables drop table t;

This will workout..