Home » SQL & PL/SQL » SQL & PL/SQL » Create table if it doesn't exist
Create table if it doesn't exist [message #205142] Thu, 23 November 2006 08:04 Go to next message
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.
Re: Create table if it doesn't exist [message #205155 is a reply to message #205142] Thu, 23 November 2006 09:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
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>




Re: Create table if it doesn't exist [message #205157 is a reply to message #205142] Thu, 23 November 2006 09:50 Go to previous messageGo to next message
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.....
Re: Create table if it doesn't exist [message #384481 is a reply to message #205142] Wed, 04 February 2009 00:50 Go to previous messageGo to next message
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
Re: Create table if it doesn't exist [message #384482 is a reply to message #384481] Wed, 04 February 2009 01:04 Go to previous message
Michel Cadot
Messages: 64103
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
Previous Topic: Autocommit after insert problem (merged)
Next Topic: Order by Clause causes slow query
Goto Forum:
  


Current Time: Sun Dec 04 14:31:28 CST 2016

Total time taken to generate the page: 0.07606 seconds