Oracle Drop Table If Exists And Create

Oracle Drop Table If Exists And Create | In this post, we will see how to drop the table if the table exists and after dropping the table how to create the table.

What is Drop?
Drop in the oracle is used to remove or delete the tables in the oracle. Using Drop we can delete a single table or multiple tables based on the condition.

Syntax of Drop:-

DROP [schemaname].TABLE tablename
[CASCADE CONSTRAINTS]
[PURGE];

Cascade constraints are optional, if it is specified it will drop all the referential integrity constraints. Purge is also optional, if it is specified then the table and all its dependable objects are removed and it cannot be recovered from the recycle bin.

Let’s see how to drop the table if it exists, if it doesn’t exist then we will create. Here we are using the stored procedure to execute the above condition specified.

Let’s create a sample table “test”, and insert some data into it.

CREATE TABLE test(id int, name VARCHAR(20));
INSERT INTO test VALUES(1, 'John');
INSERT INTO test VALUES(1, 'William');
SELECT * FROM test;

By using the DESC we will check the description of the table.

DESC test;

Output:-

Name     Null?    Type
-------- -------- -------------
ID                NUMBER
NAME              VARCHAR2(20)

Now we have created the table “test”, we will execute the query to check if the table exists and drop the table and create it.

BEGIN
   BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE test';
   EXCEPTION
       WHEN OTHERS THEN
            IF SQLCODE != -942 THEN
                RAISE;
            END IF;
   END;

   EXECUTE IMMEDIATE 'CREATE TABLE test(
     id NUMBER, name VARCHAR(20), email VARCHAR(10)
   )';

END;
/

Output:-

PL/SQL procedure successfully completed.

Now let us see the description of the table to check if the table is created.

DESC test;

Output:-

Name     Null?    Type
-------- -------- -------------
ID                NUMBER
NAME              VARCHAR2(20)
EMAIL             VARCHAR2(10)

From the above output, we can say that it has dropped the existing table and created a table, we can also check this by following the query.

SELECT * FROM test;

Output:-

no rows selected

As the table is newly created and no data inserted we will get this output. This is how we drop the existing table and create the table in Oracle.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search