"IF OBJECT_ID (N'tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;" in Oracle SQL Developer

Question

Is there an equivalent of SQL Server's IF OBJECT_ID (N'tempdb..#tmp') IS NOT NULL DROP TABLE #tmp; in Oracle?

I want to create a local temporary table without specifying column names, column details etc., insert into it any query results, and drop it if the whole transaction is rerun.

Answer 1

You do not need to check if the table exists. Use EXECUTE IMMEDITATE and try to drop the table and if it does not exist then catch the exception:

DECLARE
  does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(does_not_exist, -942);
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tmp';
  DBMS_OUTPUT.PUT_LINE('The table was dropped.');
EXCEPTION
  WHEN does_not_exist THEN
    DBMS_OUTPUT.PUT_LINE('The table did not exist.');
END;
/

---

However, if you want a private temporary table that automatically drops when the transaction is completed then:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp AS
  SELECT * FROM tmp;

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

fiddle