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!
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