Clash Royale CLAN TAG#URR8PPP

Check table exist or not before create it in Oracle

Trying to check is table exist before create in Oracle. Search for most of the post from Stackoverflow and others too. Find some query but it didn't work for me.

IF((SELECT count(*) FROM dba_tables where table_name = 'EMPLOYEE') <= 0)
THEN
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)
END IF;


Which gives me error

Error: ORA-00900: invalid SQL statement
SQLState: 42000
ErrorCode: 900
Position: 1


I search for the syntax for IF condition, I think which is also write.
Please suggest me....

IF

5 Answers
5

As Rene also commented, it's quite uncommon to check first and then create the table.
If you want to have a running code according to your method, this will be:

declare
nCount NUMBER;
v_sql LONG;

begin
SELECT count(*) into nCount FROM dba_tables where table_name = 'EMPLOYEE';
IF(nCount <= 0)
THEN
v_sql:='
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;

END IF;
end;


But I'd rather go catch on the Exception, saves you some unnecessary lines of code:

declare
v_sql LONG;
begin

v_sql:='create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;

EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL; -- suppresses ORA-00955 exception
ELSE
RAISE;
END IF;
END;
/


Please try:

SET SERVEROUTPUT ON
DECLARE
v_emp int:=0;
BEGIN
SELECT count(*) into v_emp FROM dba_tables where table_name = 'EMPLOYEE';

if v_emp<=0 then
EXECUTE IMMEDIATE 'create table EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL)';
end if;
END;


declare n number(10);

begin
select count(*) into n from tab where tname='TEST';

if (n = 0) then
execute immediate
'create table TEST ( ID NUMBER(3), NAME VARCHAR2 (30) NOT NULL)';
end if;
end;


I know this topic is a bit old, but I think I did something that may be useful for someone, so I'm posting it.

I compiled suggestions from this thread's answers into a procedure:

CREATE OR REPLACE PROCEDURE create_table_if_doesnt_exist(
p_table_name VARCHAR2,
create_table_query VARCHAR2
) AUTHID CURRENT_USER IS
n NUMBER;
BEGIN
SELECT COUNT(*) INTO n FROM user_tables WHERE table_name = UPPER(p_table_name);
IF (n = 0) THEN
EXECUTE IMMEDIATE create_table_query;
END IF;
END;


You can then use it in a following way:

call create_table_if_doesnt_exist('my_table', 'CREATE TABLE my_table (
id NUMBER(19) NOT NULL PRIMARY KEY,
text VARCHAR2(4000),
modified_time TIMESTAMP
)'
);


I know that it's kinda redundant to pass table name twice, but I think that's the easiest here.

Hope somebody finds above useful :-).

My solution is just compilation of best ideas in thread, with a little improvement.
I use both dedicated procedure (@Tomasz Borowiec) to facilitate reuse, and exception handling (@Tobias Twardon) to reduce code and to get rid of redundant table name in procedure.

DECLARE

PROCEDURE create_table_if_doesnt_exist(
p_create_table_query VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE create_table_query;
EXCEPTION
WHEN OTHERS THEN
-- suppresses "name is already being used" exception
IF SQLCODE = -955 THEN
NULL;
END IF;
END;

BEGIN
create_table_if_doesnt_exist('
CREATE TABLE "MY_TABLE" (
"ID" NUMBER(19) NOT NULL PRIMARY KEY,
"TEXT" VARCHAR2(4000),
"MOD_TIME" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
');
END;
/


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.