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.