CODEWITHSUNDEEP
sqloracle-databaseplsql
v3ctor
v3ctor

Reputation: 105

PL/SQL if table not exist create

Hello i use oracle SQL developer

I have create a procedure, and i need to check if a table exist, if not exist i must create how can do?

I have try this

DECLARE v_emp int:=0;
BEGIN
  SELECT count(*) into v_emp FROM dba_tables; 

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

but give me an error 00103 because not find table

Upvotes: 2

Views: 11620

Answers (3)

Emipro Technologies Pvt. Ltd.
Emipro Technologies Pvt. Ltd.

Reputation: 14746

You should try following,

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;

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Just execute the create and watch the exception if thrown. Oracle would never replace the DDL of a table.

declare
  error_code NUMBER;
begin
EXECUTE IMMEDIATE 'CREATE TABLE EMPLOYEE(AGE INT)';
exception
when others then
error_code := SQLCODE;
 if(error_code = -955)
 then
   dbms_output.put_line('Table exists already!'); 
 else
   dbms_output.put_line('Unknown error : '||SQLERRM); 
 end if;
end;

Upvotes: 4

Ionic
Ionic

Reputation: 3935

You can run this for example:

if (select count(*) from all_tables where table_name = 'yourTable')>0 then
-- table exists
else
-- table doesn't exist
end if;

Upvotes: 2

Related Questions