Oracle SQL - If Exists, Drop Table & Create

Question

Can some one please guide me what's wrong with this query? In SQL Server we just check the presence of the Object_ID of a table to drop it and re-create it. I am new to Oracle and wrote this query:

declare Table_exists INTEGER;
 BEGIN
 Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1';
 EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
          Table_Exists :=0;
if(table_exists)=1
  Then
  Execute Immediate 'Drop Table TABLENAME1;'
  'Create Table TABLENAME1;';
  DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else 
     Execute Immediate 'Create Table TABLENAME1;';
     DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;

I get the output - ANONYMOUS BLOCK COMPLETED, but the table is not created. The table was previously existing, so I dropped it to check if the PL/SQL is actually creating the table, but NO. What is wrong here? What am I missing? Please guide.

Answer 1

When you are using all_tables filter the results for your schema by adding where owner = 'your_schema' or use sys.user_tables

ALL_TABLES describes the relational tables accessible to the current user

USER_TABLES describes the relational tables owned by the current user.

When use execute_emmidiate remove the ; from the query;

Modified query;

DECLARE 
    Table_exists INTEGER;
BEGIN
    Select count(*) into Table_exists from sys.user_tables where table_name='TABLENAME1';
    --or
    --Select count(*) into Table_exists from sys.all_tables 
    --where table_name='TABLENAME1' and owner = 'your_DB';
    if table_exists = 1 Then
        Execute Immediate 'Drop Table TABLENAME1';
        Execute Immediate 'Create Table TABLENAME1(num number)';
        DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
    Else 
        Execute Immediate 'Create Table TABLENAME1(num number)';
        DBMS_OUTPUT.PUT_LINE('New Table Created!');
    END IF;
END;

Answer 2

First note:

Select count(*) into Table_exists
from sys.all_tables
where table_name = 'TABLENAME1';

will always return one row. You don't need the exception handling.

My best guess is that you have more than one table called TABLENAME1. Run this query to find out:

Select *
from sys.all_tables
where table_name = 'TABLENAME1';

Oracle stores tables from all owners that you can access. You might also want to check OWNER_NAME in the where clause.

However, you seem to understand exception handling. So, just drop the table, ignore any errors, and then recreate the table.