PL/SQL if table not exist create

Question

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

See my answer. Try to query `all_tables` not just ´dba_tables`. — Ionic, Jul 03 '15 at 14:03
What is the reason for writing this code? Is it a homework question? Typically, you wouldn't do this in production code, as tables are permanent objects, and shouldn't be created on the fly (there are exceptions, but these are, IME, rare). If you need a table to house data only visible to that session, you would create a Global Temporary Table (GTT) and then refer to that in your code - you wouldn't create/drop tables on the fly. — Boneist, Jul 03 '15 at 14:55
This [link](http://stackoverflow.com/questions/1799128/oracle-if-table-exists) maybe helps. — Leonardo Heis, Jul 03 '15 at 16:29

score 4 · Answer 1 · answered Jul 03 '15 at 14:16

4

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;

answered Jul 03 '15 at 14:16

Maheswaran Ravisankar

17,652
6
47
69

i don't know why when I put into procedure not run – v3ctor Jul 06 '15 at 08:49
did you enable `DBMS_OUTPUT` on ? `SET SERVERPUT ON` in `SQL*Plus` – Maheswaran Ravisankar Jul 06 '15 at 10:18
ok in that case, Choose `View -> dbms output `- > And then click the `+` icon on the tab.. – Maheswaran Ravisankar Jul 06 '15 at 10:31
the compiler give me error ORA-00942 table or view not exist, because i delete the table. I need to create if not exist – v3ctor Jul 06 '15 at 10:41
I must insert into a procedure was first do a select, i click on compile but show me ORA-00942 table or view not exist – v3ctor Jul 06 '15 at 11:00
you are selecting `dba_tables` ? – Maheswaran Ravisankar Jul 06 '15 at 11:06
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/82482/discussion-between-maheswaran-ravisankar-and-v3ctor). – Maheswaran Ravisankar Jul 06 '15 at 11:06

score 2 · Answer 2 · answered Jul 03 '15 at 14:02

2

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;

answered Jul 03 '15 at 14:02

Ionic

3,884
1
12
33

score 1 · Answer 3 · answered Jul 03 '15 at 14:49

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;

PL/SQL if table not exist create

3 Answers3