Results 1 to 11 of 11

Thread: if table exists

  1. #1
    Join Date
    Aug 2006
    Posts
    39

    Unanswered: if table exists

    Hi,
    I am a sql server developer and have been asked to create a script for drop and create tables.

    For example this is what I would like to have:
    if table1 exists
    drop table1
    create table1...

    if table2 exists
    drop table2
    create table2

    if table3 exists
    drop table3
    create table3

    I am ok with the drop and create scrip but need help on if exists please.
    Thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    declare
      cnt number;
    begin
    
      select count(*) 
      into cnt 
      from all_tables 
      where table_name = 'DEPT' and owner = 'SCOTT'; 
      
      if cnt = 1 then
        dbms_output.put_line('exists');
      else
        dbms_output.put_line('not exists');
      end if;
    
    end;
    -cf

  3. #3
    Join Date
    Aug 2006
    Posts
    39
    Hi,
    What I am really after is something as follows:
    if exists(select * from somewhere where id=tbltable1)
    begin
    drop table test.tbltable1;
    end

    if exists(select * from somewhere where id=tbltable2)
    begin
    drop table test.tbltable2;
    end

    if exists(select * from somewhere where id=tbltable3)
    begin
    drop table test.tbltable3;
    end

    create table1...
    create table2...
    create table3...

    Thank you

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    In SQL*Plus you do not have the "if exist" statement, besides, it does not matter if the table exists if you are going to create it anyway:
    Code:
    WHENEVER SQLERROR CONTINUE;
    Drop Table1;
    Create Table1...
    
    Drop Table2;
    Create Table2
    
    Drop Table3;
    Create Table3



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2006
    Posts
    39
    The idea is to have one script which includes the drop and create of the three tables.
    In that case i can not ignore the error. Am I right?
    Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >In that case i can not ignore the error. Am I right?
    Are you unwilling or incapable of running your own tests to answer your own questions?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2006
    Posts
    39
    Hi,
    The sql that I am building has the following sql in it:

    if cnt = 1 then
    drop table test.table1;

    end if;

    is this correct? because when running it it gives an <error> infront of drop.
    Should there be a begin end there too?
    Thanks

  8. #8
    Join Date
    Aug 2006
    Posts
    39
    Hello again,
    This is what I have comeup with but it seems there is an error on the first drop.
    Thanks

    declare cnt number;

    begin
    select count(*) into cnt from all_tables
    where table_name = 'test.table1';

    if cnt = 1 then
    drop table test.table1;
    end if;

    select count(*) into cnt from all_tables
    where table_name = 'test.table2';

    if cnt = 1 then
    drop table 'test.table2';
    end if;

    select count(*) into cnt from all_tables
    where table_name = 'test.table3';

    if cnt = 1 then
    drop table test.table3;
    end if;

    CREATE TABLE test.table1
    (
    tcountry VARCHAR2 (50) NOT NULL
    , tdatatype VARCHAR2 (50) NOT NULL
    , dteffectivedate DATE
    , ifamilysize NUMBER (2)
    , tgrade VARCHAR2 (50)
    , damount NUMBER (15,2)
    );



    CREATE TABLE test.table2
    (
    thomecountry VARCHAR2 (50) NOT NULL
    , thostcountry VARCHAR2 (50) NOT NULL
    , tdatatype VARCHAR2 (50) NOT NULL
    , dteffectivedate DATE
    , ifamilysize NUMBER (2)
    , tgrade VARCHAR2 (50)
    , damount NUMBER (15,2)
    );


    CREATE TABLE test.table3
    (
    ilocid VARCHAR2 (50) NOT NULL
    , tdatatype VARCHAR2 (50) NOT NULL
    , dteffectivedate DATE
    , ifamilysize NUMBER (2)
    , tgrade VARCHAR2 (50)
    , damount NUMBER (15,2)
    );

    end

    commit;

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    SQL*Plus is NOT WinDoze SQL Server.

    You may want to start learning by reading the manual.


    Last edited by LKBrwn_DBA; 11-09-06 at 13:15.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    commands like drop and create may not be run bare in a sql block in oracle. See the execute immediate command.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here is a sample script. For testing purposes I created 3 tables and used them in the WHERE clause of the cursor (i.e. I didn't want to drop ALL tables in my schema). You could use another tables or even omit the whole WHERE clause.
    Code:
    SQL> CREATE TABLE TAB_1 (a NUMBER);
    
    Table created.
    
    SQL> CREATE TABLE TAB_2 (b NUMBER);
    
    Table created.
    
    SQL> CREATE TABLE TAB_3 (c NUMBER);
    
    Table created.
    
    SQL> DECLARE
      2    CURSOR cur_tab IS
      3    SELECT table_name FROM user_tables
      4                  WHERE table_name IN ('TAB_1', 'TAB_2', 'TAB_3');
      5  BEGIN
      6    FOR cur_r IN cur_tab
      7    LOOP
      8    NULL;
      9      IF cur_tab%FOUND
     10     THEN
     11        EXECUTE IMMEDIATE 'drop table ' || cur_r.table_name;
     12     END IF;
     13    END LOOP;
     14  END;
     15  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * From tab;
    
    
    TNAME                          TABTYPE  CLUSTERID
    ------------------------------ ------- ----------
    DEPT                           TABLE
    EMP                            TABLE
    BONUS                          TABLE
    SALGRADE                       TABLE
    TEST                           TABLE
    How to create tables? I'd put CREATE TABLE statements into an .SQL script file and run it at the SQL prompt as

    SQL> @create_my_tables

    If you want, you can include CREATE TABLE statement into the PL/SQL script (again using EXECUTE IMMEDIATE), but it promises to be difficult to maintain if tables are anything but very simple ones.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •