1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

if table exists

Discussion in 'SQL PL/SQL' started by simona007, May 24, 2009.

  1. simona007

    simona007 Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Hi everyone

    I have a small query. I have moved from sql server to oracle as i said before :) Now I want to create a script for dropping and creating some tables.

    For instance something like this
    Code (Text):

    if table1 exists
    drop table1
    create table1...

    if table2 exists
    drop table2
    create table2

    if table3 exists
    drop table3
    create table3
     
    so is there any way to check in oracle if a particular table exists or not?

    Thanks
    Simona
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    well you can query the view all_tables when writing the script to find out if the table exists or not.
     
    simona007 likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also take care of Foreign keys while dropping the tables. And need to recreate the foreign key once table is recreated.

    Code (SQL):
    DROP TABLE <table_name> cascade constraints;
     
     
    simona007 likes this.
  4. simona007

    simona007 Active Member

    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    80
    Hi guys thanks all for your help. This is what I came up with and it is working.
    Code (SQL):

    DECLARE
       v_cnt   NUMBER := 0;
    BEGIN
       SELECT COUNT (*)
         INTO v_cnt
         FROM all_tables
        WHERE TABLE_NAME = 'TABLE1' AND owner = 'TEST';

       IF v_cnt = 1
       THEN
          EXECUTE IMMEDIATE 'DROP TABLE TEST.TABLE1 CASCADE CONSTRAINTS;';

          EXECUTE IMMEDIATE '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)
                             );'
    ;
       END IF;
    END;