Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    7

    Unanswered: Checking for Existing tables before creating

    HI,
    I have scripts which build a DB, tables and users etc, but what I can't seem to do is check if they exist 1st before I run the scripts.

    I have tried the EXISTS command as in :
    select Count(TABLE_NAME) from all_tables where EXISTS (select TABLE_NAME from all_tables where TABLE_NAME='ADDRESS'
    This returns all the table names because ADDRESS exists, but how do I interperate what it returns?
    Is it a BOOLEAN or Numeric Value.

    AS I though I would be able to do something like this below but they both returns errors:

    NUMERIC:
    ------------
    BEGIN
    IF ( select Count(TABLE_NAME) from all_tables where EXISTS (select TABLE_NAME from all_tables where TABLE_NAME='ADDRESS' ) <> NULL THEN
    DROP TABLE ADDRESS;
    END IF
    END;

    BOOLEAN:
    -----------
    BEGIN
    IF ( select Count(TABLE_NAME) from all_tables where EXISTS (select TABLE_NAME from all_tables where TABLE_NAME='ADDRESS' ) =TRUE THEN
    DROP TABLE ADDRESS;
    END IF
    END;


    Any help ?

    Thanks Nick

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Checking for Existing tables before creating

    The PL/SQL syntax would be:

    Code:
    BEGIN
      FOR r IN (SELECT 1 FROM user_tables WHERE table_name = 'ADDRESS')
      LOOP
        EXECUTE IMMEDIATE 'DROP TABLE ADDRESS';
      END LOOP;
    END;
    /
    But the easiest way is just to do the DROP TABLE regardless and ignore the error:

    SQL> ed junk.sql:
    DROP TABLE foo;
    CREATE TABLE foo(id NUMBER);
    {EOF}

    SQL> @junk
    DROP TABLE foo
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist



    Table created.

  3. #3
    Join Date
    Apr 2003
    Posts
    7
    Thanks for the info but the EXECUTE IMMEDIATE does not appear to work.

    Get this reply:

    EXECUTE IMMEDIATE 'DROP TABLE ADDRESS';
    *
    ERROR at line 3:
    ORA-06550: line 3, column 9:
    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
    := . ( @ % ;
    The symbol ":= was inserted before "IMMEDIATE" to continue.

    I'm using Oracle 9.

    Has anybody else seen this !!

    Cheers Nick

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by nickp
    Thanks for the info but the EXECUTE IMMEDIATE does not appear to work.

    Get this reply:

    EXECUTE IMMEDIATE 'DROP TABLE ADDRESS';
    *
    ERROR at line 3:
    ORA-06550: line 3, column 9:
    PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
    := . ( @ % ;
    The symbol ":= was inserted before "IMMEDIATE" to continue.

    I'm using Oracle 9.

    Has anybody else seen this !!

    Cheers Nick
    EXECUTE IMMEDIATE is a PL/SQL command, it doesn't work in SQL Plus except between BEGIN and END of a PL/SQL block.

    In SQL Plus you would just do:

    SQL> DROP TABLE ADDRESS;

  5. #5
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107
    Hi,
    Just to add to what Andrew just mentioned.
    Doing it in PL/SQL would be :

    BEGIN
    BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE address';
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Table Does Not Exist');
    END;

    BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE address (col1 VARCHAR2(100))';
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Table Creation Error');
    END;
    END;
    /

    Hope it Helps
    nn

  6. #6
    Join Date
    Apr 2003
    Posts
    7

    Smile EXECUTE IMMEDIATE

    Thanks for all the help the scripts working fine now.

    Thanks again

    Cheers Nick

Posting Permissions

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