dropping a global temporary table

Question

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

User · Answer

-- First Truncate temporary table SQL  TRUNCATE TABLE test temp1   -- Then Drop temporary table SQL  DROP TABLE test temp1

User · Answer

Step 1  Figure out which errors you want to trap   If the table does not exist   SQL gt  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 gt  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 gt  insert into t values   whatever     1 row created    Back in the first session  attempt to drop   SQL gt  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 gt  drop table t   Table dropped   SQL gt  exec p  Table t did not exist at time of drop  Continuing      PL SQL procedure successfully completed    And now  with t in use   SQL gt  create global temporary table t  data varchar2 4000     Table created    In another session   SQL gt  insert into t values  null    1 row created    And then in the first session   SQL gt  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

User · Answer

Down the apache server by running below in putty cd  ADMIN SCRIPTS HOME   adstpall sh Drop the Global temporary tables drop table t    This will workout

User · Answer

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

User · Answer

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

[oracle] dropping a global temporary table

Examples related to oracle

Examples related to plsql

Examples related to ddl

Examples related to temp-tables