CREATE TABLE IF NOT EXISTS on Oracle database

By HyperTesto | A BIT of everything | 12 Aug 2019

$0.02 tipped


Since the original article on my website is receiving a bit of attention by non-italian speaking people, i'll translate it here as my first story! I hope it will be useful to you!

Some time ago i had to write an SQL script for Oracle DB to create a table only if it wasn't already existing in the current schema.

Firstly i thought something like a mySQL CREATE TABLE IF NOT EXISTS could make the trick.

CREATE TABLE IF NOT EXISTS table_name
    (columns definition,...)
    [table options]
    [partition options]

Easy, isn't it? No! It wasn't working on Oracle.

A bit of research led me to an answer on Stackoverflow:

Normally, it doesn’t make a lot of sense to check whether a table exists or not because objects shouldn’t be created at runtime and the application should know what objects were created at install time. If this is part of the installation, you should know what objects exist at any point in the process so you shouldn’t need to check whether a table already exists.

Makes sense so to me, even if i don't like it.
On the same answer there are three possible solutions:

If you really need to, however,

  • You can attempt to create the table and catch the `ORA-00955: name is already used by an existing object" exception.
  • You can query USER_TABLES (or ALL_TABLES or DBA_TABLES depending on whether you are creating objects owned by other users and your privileges in the database) to check to see whether the table already exists.
  • You can try to drop the table before creating it and catch the `ORA-00942: table or view does not exist" exception if it doesn't.

I prefer the try-catch logic of the first solution, however since i'm not an Oracle expert, i am sure there are better solutions out in the web.

This is what i came up with:

declare
begin
  execute immediate 'create table "TABELLA" ("ID" number not null)';
  exception when others then
    if SQLCODE = -955 then null; else raise; end if;
end;

On Oracle DB documentation you can find all the error codes you can catch.

If you have better knowledge of oracle database i can update the article with your suggestions!


HyperTesto
HyperTesto

I'm a multimedia software developer for a small IT company. In my free time i break my Linux install and write on my blog


A BIT of everything
A BIT of everything

My notes about Linux, programming and everything else.

Send a $0.01 microtip in crypto to the author, and earn yourself as you read!

20% to author / 80% to me.
We pay the tips from our rewards pool.