The very useful CREATE TABLE IF NOT EXISTS syntax was finally introduced in Oracle Database – Oracle Database 23c to be precise. This syntax allows us to run a CREATE TABLE statement without getting an error if the table already exists.

Earlier versions of Oracle don’t support the IF NOT EXISTS clause, and so if we want to avoid any nasty errors resulting from trying to create a table that already exists, we need to do a bit of extra work.

Option 1: The CREATE TABLE IF NOT EXISTS Statement

As mentioned, the CREATE TABLE IF NOT EXISTS syntax was introduced in Oracle Database 23c. This is a concise way of avoiding an error in the event that there’s already a table with the same name that we’re trying to create.

Example:

CREATE TABLE IF NOT EXISTS t1 (
  c1 number(6,0),
  c2 varchar2(10)
);

That creates a table called t1. The statement will run without error, even if there’s already a table with that name in the database.

Option 2: Check the DBA_TABLES View

DBA_TABLES is a data dictionary view that describes all relational tables in the database. Its columns are the same as those in ALL_TABLES.

We can check this view to see if the table already exists, then only run the CREATE TABLE statement if it doesn’t already exist.

Example:

DECLARE
tbl_count number;
sql_stmt long;

BEGIN
    SELECT COUNT(*) INTO tbl_count 
    FROM dba_tables
    WHERE owner = 'HR'
    AND table_name = 'T1';

    IF(tbl_count <= 0)
        THEN
        sql_stmt:='
        CREATE TABLE T1 (
            c1 number(6,0),
            c2 varchar2(10)
        )';
        EXECUTE IMMEDIATE sql_stmt;
    END IF;
END;

Result:

PL/SQL procedure successfully completed.

In this case, the table was created because no existing table had the name t1.

Now, if we try to create the table again, we’ll get the same output that the PL/SQL procedure successfully completed, and there will be no error.

However, if we simply try to create the table without first checking for its existence, we get an error:

CREATE TABLE T1 (
    c1 number(6,0),
    c2 varchar2(10)
);

Result:

Error report -
ORA-00955: name is already used by an existing object
00955. 00000 -  "name is already used by an existing object"

Option 3: Test for the Error

Another way to do it is to simply go ahead and run the CREATE TABLE statement, and then catch any ORA-00955 error that occurs. Specifically, we catch any SQLCODE -955 error that occurs.

Example:

DECLARE
sql_stmt long;

BEGIN
    sql_stmt:='
    CREATE TABLE T1 (
        c1 number(6,0),
        c2 varchar2(10)
    )';
    EXECUTE IMMEDIATE sql_stmt;

    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -955 THEN
                NULL;
            ELSE
                RAISE;
            END IF;
END;

Result:

PL/SQL procedure successfully completed.

I ran that even though the T1 table already existed. The ORA-00955 error was caught and handled so we didn’t get an error message, and the table wasn’t created.

If the table hadn’t already existed, the table would have been created, and we’d see the same output.