Drop a Temp Table If Exists In Oracle-CodePudding

I am a new user to Oracle but have advanced knowledge of SQL Server. When working with temp tables in SQL Server we would always check to see if the table existed and drop it if it did before creating a new one. This would allow for the SQL script to be executed over and over without an error.

IF OBJECT_ID(N'tempdb..#CnsmrHstRwNm', N'U') IS NOT NULL
DROP TABLE #CnsmrHstRwNm;
CREATE TABLE #CnsmrHstRwNm;

I cannot seem to be able to do the same in Oracle. I have researched several ideas, including one who said it was a bad idea, but none of them have worked. Here is one scenario that I have tried. Does someone have any ideas?

I've referenced Oracle: If Table Exists and it seems like this could work, but I cannot figure out how to create the table after dropping it.

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || 'MY_TEMP_TABLE';
EXCEPTION
   WHEN OTHERS THEN NULL;
   CREATE GLOBAL TEMPORARY TABLE MY_TEMP_TABLE (a varchar2(1), b varchar2(1));
END;

Can anyone help?

CodePudding user response：

I'd like to second anyone who said that - in Oracle - we don't drop/create tables within PL/SQL. That's just a bad idea. Create table once, use it as many times as you want. Delete (or truncate) its contents, insert/update rows, but don't (re)create it over and over again.

Anyway, here's how; I included message so that you could follow what's going on.

At first, table doesn't exist:

SQL> desc test
ERROR:
ORA-04043: object test does not exist

Run the script:

SQL> set serveroutput on
SQL> begin
  2    begin
  3      dbms_output.put_line('Trying to drop a table');
  4      execute immediate 'drop table test';
  5      dbms_output.put_line('Table dropped');
  6    exception
  7      when others then
  8        dbms_output.put_line('Error when dropping the table: ' || sqlerrm);
  9        null;
 10    end;
 11    dbms_output.put_line('Creating a table');
 12    execute immediate 'create table test (a varchar2(1))';
 13    dbms_output.put_line('Table created');
 14  end;
 15  /
Trying to drop a table
Error when dropping the table: ORA-00942: table or view does not exist
Creating a table
Table created

PL/SQL procedure successfully completed.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(1)

Right; table now exists. You'd use it and - later - run the script once again:

SQL> begin
  2    begin
  3      dbms_output.put_line('Trying to drop a table');
  4      execute immediate 'drop table test';
  5      dbms_output.put_line('Table dropped');
  6    exception
  7      when others then
  8        dbms_output.put_line('Error when dropping the table: ' || sqlerrm);
  9        null;
 10    end;
 11    dbms_output.put_line('Creating a table');
 12    execute immediate 'create table test (a varchar2(1))';
 13    dbms_output.put_line('Table created');
 14  end;
 15  /
Trying to drop a table
Table dropped
Creating a table
Table created

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

Rather than using a GLOBAL TEMPORARY TABLE, if you use a PRIVATE TEMPORARY TABLE then it automatically drops when the transaction is completed:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp (
  a varchar2(1), b varchar2(1)
);

Then when you COMMIT the table is automatically dropped (and you can recreate it in the next transaction without having to drop and recreate it).

However, if you do want a permanent table then use EXECUTE IMMEDIATE to drop and create it and catch (and ignore) the exception if the table does not exist:

DECLARE
  does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(does_not_exist, -942);
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE MY_TEMP_TABLE';
  EXCEPTION
    WHEN does_not_exist THEN
      NULL;
  END;
  EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE MY_TEMP_TABLE (a varchar2(1), b varchar2(1))';
END;
/