Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

using exists to create table

643412Nov 15 2012 — edited Nov 15 2012
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 13 2012
Added on Nov 15 2012
11 comments
1,007 views