OraFAQ Forum: SQL & PL/SQL » Create table if it doesn't exist

Home » SQL & PL/SQL » SQL & PL/SQL » Create table if it doesn't exist

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create table if it doesn't exist [message #205142]

Thu, 23 November 2006 08:04

alexh2084
Messages: 7
Registered: November 2006
Location: Brasov

Junior Member

Hi,

I'm new to Oracle and i have a big problem: i have an sql script file written for MySql and i have to rewrite it for Oracle

The file contains only sql statement like create table, select, update and delete and doesn't contain procedures.

i have to delete and create a table but only if it exists... in mysql the instructions are:

drop table if exists user;
.............
create temporary table if not exists tmp_pwd (pwd varchar(28));
.............

i haven't found in Oracle the equivalent of the "if exists" statement. I have searched the net and i saw that after if you cannot use the "exists" clause and that "if" can be used only after "where"

The other solutions i have found used Pl/SQL but i cannot use them. I have to use only sql statements that will be saved in a file and the executed using Ant

Any suggestions appreciated.
Thanks in advance.

Report message to a moderator

Re: Create table if it doesn't exist [message #205155 is a reply to message #205142]

Thu, 23 November 2006 09:30

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Hi,

I'm not aware of a way to do it without PL/SQL.

Can't you put a SQL like this in the ANT script ?

BEGIN
    EXECUTE IMMEDIATE ('DROP TABLE test');
    EXECUTE IMMEDIATE ('CREATE TABLE test(x number(1))');
    Dbms_Output.put_line('CHECK -> : Table recreated');
    EXCEPTION
     WHEN OTHERS THEN 
         Dbms_Output.put_line('CHECK -> : Table does not exist');
END;
/

That way it tries to drop / create the table, but if the table does not exist it jumps to the exception handler.

In SQL Plus it works :

SQL> -- enable output
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> -- create table
SQL> CREATE TABLE test (x NUMBER(1));

Table created.

SQL>
SQL> -- try script with table present
SQL> BEGIN
  2      EXECUTE IMMEDIATE ('DROP TABLE test');
  3      EXECUTE IMMEDIATE ('CREATE TABLE test(x number(1))');
  4      Dbms_Output.put_line('CHECK -> : Table recreated');
  5      EXCEPTION
  6       WHEN OTHERS THEN
  7           Dbms_Output.put_line('CHECK -> : Table does not exist');
  8 END;
  9 /
CHECK -> : Table recreated

PL/SQL procedure successfully completed.

SQL> -- drop table
SQL> drop TABLE test;

Table dropped.

SQL>
SQL> -- try script without table present
SQL> BEGIN
  2      EXECUTE IMMEDIATE ('DROP TABLE test');
  3      EXECUTE IMMEDIATE ('CREATE TABLE test(x number(1))');
  4      Dbms_Output.put_line('CHECK -> : Table recreated');
  5      EXCEPTION
  6       WHEN OTHERS THEN
  7           Dbms_Output.put_line('CHECK -> : Table does not exist');
  8  END;
  9  /
CHECK -> : Table does not exist

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: Create table if it doesn't exist [message #205157 is a reply to message #205142]

Thu, 23 November 2006 09:50

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Have you looked att GTT, Oracle's Global Temporary Tables ?

I seldom use them but it might be an option in your case.
Then you can skip the "drop" and "create" part, and all the
execute immediate.....

Report message to a moderator

Re: Create table if it doesn't exist [message #384481 is a reply to message #205142]

Wed, 04 February 2009 00:50

kaarthiik
Messages: 1
Registered: February 2009

Junior Member

Here is the Oracle SP that I use as equvivalent for "Drop table if exists" in Mysql/if exists in SQL Server

create or replace PROCEDURE DROP_TABLE(TabName in Varchar2)
IS
temp number:=0;
tes VARCHAR2 (200) := TabName;
drp_stmt VARCHAR2 (200):=null;
BEGIN
select count(*) into temp from user_tables where TABLE_NAME = tes ;
if temp =1 then
drp_stmt := 'Drop Table '||tes;
EXECUTE IMMEDIATE drp_stmt;
end if;

END;
-----------------
call DROP_TABLE ('<Table name>')
will drop if the table exists, else nothing Smile

Report message to a moderator

Re: Create table if it doesn't exist [message #384482 is a reply to message #384481]

Wed, 04 February 2009 01:04

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Maybe between "select" and "drop" the table no more exists.
A better way, just drop it and ignore the exception saying the table does not exist.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel

Report message to a moderator

Previous Topic:	Autocommit after insert problem (merged)
Next Topic:	Order by Clause causes slow query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 10:23:59 CDT 2024