This discussion is archived
11 Replies Latest reply: Nov 15, 2012 12:47 PM by 643412 RSS

using exists to create table

643412 Newbie
Currently Being Moderated
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


I asked a question here about some other code I was writing a little while back, and it was recommended that I try to incorporate a different style in my block.
So I'm trying to use the exists statement to check if a table exists. If it does, tell me. If it doesn't, create it.
BEGIN
   IF exists(select table_name from all_tables
                where table_name = 'WILLY_TEST')  
    then dbms_output.put_line('TABLE EXISTS');  
    else
CREATE TABLE FS_NRIS_FSVEG.WILLY_TEST  
(
  TEST_COL_1  VARCHAR2(1)                       NOT NULL,
  TEST_COL_2  VARCHAR2(1)                       NOT NULL
)
RESULT_CACHE (MODE DEFAULT)
STORAGE    (
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

ALTER TABLE FS_NRIS_FSVEG.WILLY_TEST ADD (
  CONSTRAINT WILLY_TESTS_PK
  PRIMARY KEY
  (TEST_COL_1)
  ENABLE VALIDATE);
end if;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Create table failed');
END;
/
I've tried it both as above, as well as with the EXECUTE IMMEDIATE and the block in Q ticks. (Q'{.....}';
I can get it to complete successfully, but it's a lie, because nothing happens. It doesn't create the table, nor does it error and tell me.

What am I missing or doing wrong? From what I've read, I have the exists function correct, the iif statement correct...I don't get it.

Thanks for your help.
  • 1. Re: using exists to create table
    sb92075 Guru
    Currently Being Moderated
    delete, remove, & eliminate all EXCEPTION code
    For reason why check these links.

    http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

    http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

    http://tkyte.blogspot.com/2007/03/challenge.html
  • 2. Re: using exists to create table
    L-MachineGun Pro
    Currently Being Moderated
    Willy_B wrote:

    So I'm trying to use the exists statement to check if a table exists. If it does, tell me. If it doesn't, create it.

    . . . Etc . . .

    I've tried it both as above, as well as with the EXECUTE IMMEDIATE and the block in Q ticks. (Q'{.....}';
    I can get it to complete successfully, but it's a lie, because nothing happens. It doesn't create the table, nor does it error and tell me.

    What am I missing or doing wrong? From what I've read, I have the exists function correct, the iif statement correct...I don't get it.
    1) You cannot use "EXISTS" in that manner because EXISTS is a function that tells you whether the specified element of a varray or nested table exists.
    2) You need to use EXECUTE IMMEDIATE to execute the ddl.
    :p
  • 3. Re: using exists to create table
    643412 Newbie
    Currently Being Moderated
    As I stated, I did have the execute immediate in there, but it failed...I've tried it both ways.

    But I don't understand why I cant use that function to return the test?


    Thanks for the quick responses. :)
  • 4. Re: using exists to create table
    Purvesh K Guru
    Currently Being Moderated
    Your Modified Block:
    set serveroutput on;
    declare
      v_count     number;
    BEGIN
      select count(*) 
       into v_count
       from all_tables
       where table_name = 'WILLY_TEST';
       
       IF (v_count >= 1 )
        then dbms_output.put_line('TABLE EXISTS');  
        else
    execute immediate 'CREATE TABLE HR.WILLY_TEST  
    (
      TEST_COL_1  VARCHAR2(1)                       NOT NULL,
      TEST_COL_2  VARCHAR2(1)                       NOT NULL
    )
    RESULT_CACHE (MODE DEFAULT)
    STORAGE    (
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
    LOGGING 
    NOCOMPRESS 
    NOCACHE
    NOPARALLEL
    NOMONITORING';
     
    execute immediate 'ALTER TABLE HR.WILLY_TEST ADD (
      CONSTRAINT WILLY_TESTS_PK
      PRIMARY KEY
      (TEST_COL_1)
      ENABLE VALIDATE)';
    end if;
    EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Create table failed');
    END;
    / 
    You should consider using USER_TABLES view to check if the table exists, since the same table may exist for another user and this shall prevent you from creating the table.

    However, why do you want to verify if the table exists? You can directly fire the Create Table statement and Oracle shall throw an exception if it already exists else shall create it. I find it un-necessary.

    Moreover, Exists is an SQL keyword and is not available for PL/SQL.

    I also wonder, how your code executed and did not return any output. For me, Oracle 11g XE, it threw error for usage of Exists.
  • 5. Re: using exists to create table
    6363 Guru
    Currently Being Moderated
    Willy_B wrote:

    I can get it to complete successfully, but it's a lie, because nothing happens.
    It is not a lie it compiled with no errors. When you use execute immediate the string that contains SQL is not validated at compile time, because it cannot be validated at compile time, because it is possible it will change before it is run. Execute immediate is validated only at run time.
    It doesn't create the table, nor does it error and tell me.
    It does error, it does not tell you because you intentionally added code to state that you did not want to be told of any errors that happened during run time here -
    EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Create table failed');
    What am I missing or doing wrong?
    Database objects should not be created at run time in PL/SQL
    Execute immediate should not be used to circumvent this
    Execute immediate should not be used by anyone who does not understand what it is doing
    WHEN OTHERS should not be used to hide errors particularly when not followed by RAISE

    Just create the table in a SQL script. If it already exists it will not be created.
  • 6. Re: using exists to create table
    643412 Newbie
    Currently Being Moderated
    I do all my coding in the dev environment as I'm sure all of you do too.
    When we do a release, the master script might execute 200 scripts. I have to run/test it in my environment before it's run by a DBA in another STATE at a higher level.
    My code has to be able to be run multiple times without any kind of failure. It can ONLY write the error to the log.
    If it fails, my entire master script will get kicked back.

    SO... Before, I was using the exceptions to tell me if the script passed or failed. At first I was using the whole "When others then null", but after getting my wrist slapped here, switched over to using the output line syntax to specifically return what was happening.

    After asking questions here, I was told that I was basically using inefficient technique and I should look to doing it more in the front (my tech terms) so that it checks first and then executes instead of the other way.
    So my approach here is that I would check first and either notify existence or build.

    If there is yet ANOTHER more efficient way, then I'm all ears as I'm buried in learning mode here.

    Thanks!
  • 7. Re: using exists to create table
    Purvesh K Guru
    Currently Being Moderated
    Willy_B wrote:
    I do all my coding in the dev environment as I'm sure all of you do too.
    When we do a release, the master script might execute 200 scripts. I have to run/test it in my environment before it's run by a DBA in another STATE at a higher level.
    My code has to be able to be run multiple times without any kind of failure. It can ONLY write the error to the log.
    If it fails, my entire master script will get kicked back.
    I agree to few points and disagree to another few. The Install Script is meant to be run only once in an environment. It is a flawed logic/process to execute the same Install script again and again, and still expect it to execute without any errors. Yes, people will have there arguments to this, but I believe it to be flawed.

    >
    SO... Before, I was using the exceptions to tell me if the script passed or failed. At first I was using the whole "When others then null", but after getting my wrist slapped here, switched over to using the output line syntax to specifically return what was happening.
    While DBA executing the script (200 in number), is (s)he going to monitor the DBMS output? I do not think so. Exception when others should not be used in this situation at all, especially when you know the exceptions you will be facing. You should be writing the outcome to a Log file and Spool the execution.
    After asking questions here, I was told that I was basically using inefficient technique and I should look to doing it more in the front (my tech terms) so that it checks first and then executes instead of the other way.
    So my approach here is that I would check first and either notify existence or build.

    If there is yet ANOTHER more efficient way, then I'm all ears as I'm buried in learning mode here.
    I would rather say the People calling your previous technique as "Inefficient" are "Inefficient" :D
  • 8. Re: using exists to create table
    643412 Newbie
    Currently Being Moderated
    I agree to few points and disagree to another few. The Install Script is meant to be run only once in an environment.
    It is a flawed logic/process to execute the same Install script again and again, and still expect it to execute without any errors. 
    Yes, people will have there arguments to this, but I believe it to be flawed.
    The reason it gets run multiple time is that the initial push is run. Then there are two rounds of testing. If the testers find something, I tweak what ever they discover needs fixing. Then I update my master script with that tweak.
    Round2 testing comes and the other DBA runs the updated master script again...and testing happens again. Normally there are two to three rounds of testing before the code is "PROD APPROVED" and the DBA runs it in PROD.

    While DBA executing the script (200 in number), is (s)he going to monitor the DBMS output? I do not think so. 
    Exception when others should not be used in this situation at all, especially when you know the exceptions you will be facing. 
    You should be writing the outcome to a Log file and Spool the execution.
    She does sit back and babysit the whole thing, but doesn't interract. Only notes if the script breaks or not. Once completed, she sends me the spooled log. At that point, I look for any error messages that are in the log and address those and tweak from there if needed.
  • 9. Re: using exists to create table
    Purvesh K Guru
    Currently Being Moderated
    Willy_B wrote:
    She does sit back and babysit the whole thing, but doesn't interract.
    Way to go my friend!!!! ;)
  • 10. Re: using exists to create table
    643412 Newbie
    Currently Being Moderated
    ? I don't follow that.
  • 11. Re: using exists to create table
    643412 Newbie
    Currently Being Moderated
    Thanks again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points