0

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
California Dreaming
  • 15
  • 4
  • I literally know nothing of PL/SQL or Oracle but does this answer your question? [Oracle: If Table Exists](https://stackoverflow.com/questions/1799128/oracle-if-table-exists) – Thom A Jan 04 '23 at 15:30
  • Side note, `DROP TABLE IF EXISTS` syntax has been available in SQL Server for some time now too. – Thom A Jan 04 '23 at 15:31
  • Hi @Larnu, thanks for replying. I am trying different versions from the link but they give me errors like "Usually a PL/SQL compilation error." – California Dreaming Jan 04 '23 at 15:51

1 Answers1

2

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Hi @MT0. How can I incorporate the above into a query like: IF OBJECT_ID (N'tempdb..#temp') IS NOT NULL DROP TABLE #temp; select * into #temp from tableabc – California Dreaming Jan 04 '23 at 15:57
  • @CaliforniaDreaming If you have dropped the table you cannot insert into it because it does not exist. You need to recreate it first. If you wanted a permanent table then `CREATE TABLE temp AS SELECT * FROM tableabc`. (However, it seems to be an XY problem as you seem to be very fixed on recreating an SQL Server method of doing things rather than trying to work out how Oracle would normally do it). – MT0 Jan 04 '23 at 16:00
  • I am exactly asking how Oracle would do what I want, and I can see it's not as fast and easy as in SQL Server. Thanks for your reply. – California Dreaming Jan 04 '23 at 16:06
  • @CaliforniaDreaming Just use a private temporary table and then you do not need to drop it at the end of the transaction as it happens automatically. As I said, what you want appears to be an XY-problem and if you do it the Oracle way rather than trying to recreate the SQL Server way then it becomes much easier. – MT0 Jan 04 '23 at 16:13
  • thanks a lot!! @MT0 this is exactly what I needed! and I just always keep ora$ptt_ and then can add extra characters. – California Dreaming Jan 04 '23 at 17:54