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.

Four issues: 1) you don't have an Execute Immediate in front of your first create table. 2) your create table statements don't include columns. 3) your if then else block is inside and therefore part of your exception handler. 4) aggregate queries always return at least 1 row so your exception handler will never get called and your code will never run. — Sentinel
– Sentinel, Commented Jul 23, 2015 at 15:26
@Sentinel : 4) aggregate queries always return at least 1 row so your exception handler will never get called and your code will never run - Thanks for highlighting. — Sidhu Ram
– Sidhu Ram, Commented Jul 24, 2015 at 8:40

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 23, 2015 at 11:39

Praveen

9,4534 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sidhu Ram Over a year ago

Hey, that worked. Looks like the EXCEPTION HANDLING was causing the issue. I had dropped the table prior to executing the PLSQL. So the script executed saying - "New Table Created!". When I executed it again, I was expecting "Table Dropped and Re-Created!", but I got an error - "name is already used by an existing object". So the first part of the Execute Immediate didnt trigger? Why is that? Any idea?

bob dylan Over a year ago

This is the correct answer. tutorialspoint.com/plsql/plsql_exceptions.htm has some information on exceptions. You could have debugged your script by outputting the table_exists variable and seen that it was blank.

Gordon Linoff · Accepted Answer · 2015-07-23 11:18:51Z

1

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.

answered Jul 23, 2015 at 11:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Sidhu Ram Over a year ago

Hey, thanks for the input. There is no TableName1 in the schema. I have verified it. I would want to follow this coding standard so that all developers in team stick to it when they send scripts to the DBA for execution. That was how I did when I worked in SQL Server. The DBA would not execute scripts if he did not find an If Exists - Drop - Create syntax in the script.

Gordon Linoff Over a year ago

@SidhuRam . . . Your missing the point. sys.all_tables is checking all schemas, not just the current schema. Although this behavior is the same on SQL Server, it seems different, because Oracle really has only one database per server. What look like separate databases are really just separate schemas inside the database.

Sidhu Ram Over a year ago

Gordon, Thanks for the explanation. In SQL Server, we can create multiple databases on the same server. I thought the architecture in Oracle would be the same. Thanks for highlighting the difference.

Klas Lindbäck · Accepted Answer · 2015-07-23 12:00:42Z

0

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;

edited Jul 23, 2015 at 12:00

answered Jul 23, 2015 at 11:50

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

1 Comment

Sidhu Ram Over a year ago

Great! Worked perfectly. Thanks a lot :)

Collectives™ on Stack Overflow

Oracle SQL - If Exists, Drop Table & Create

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related