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;