Sidhu Ram Sidhu Ram - 1 month ago 11
SQL Question

Oracle SQL - If Exists, Drop Table & Create

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

The EXCEPTION clause lasts till the next END and not just the next statement. If you want to continue after catching the exception you need to add an additional BEGIN/END:

declare 
    Table_exists INTEGER; 
BEGIN 
    BEGIN
        Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1'; 
    EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        Table_Exists :=0; 
    END;

    if(table_exists)=1 Then 
        Execute Immediate 'Drop Table TABLENAME1;'     
        Execute Immediate '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;

As pointed out by Gordon, the EXCEPTION clause is not really needed in this case since count(*) will always return one row. So the following is sufficient:

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

    if(table_exists)=1 Then 
        Execute Immediate 'Drop Table TABLENAME1;'     
        Execute Immediate '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;