[oracle] dropping a global temporary table

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?

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

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

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

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

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