SQL works fine in SQL developer, throws ORACLE exception in SQL PLus
SELECT count(*) INTO cnt
WHERE table_name = 'SAMPLE_TABLE_01';
IF cnt = 0 THEN
execute immediate 'CREATE TABLE SAMPLE_TABLE_01
sample_column02 VARCHAR2 (128),
sample_column03 VARCHAR2 (255) NOT NULL,
CONSTRAINT sample_primary_key_pk PRIMARY KEY (sample_column03) )' ;
execute immediate 'CREATE INDEX sample_index01 ON SAMPLE_TABLE_01 (sample_column01 ASC,sample_column03 ASC)';
DBMS_OUTPUT.PUT_LINE('SAMPLE_TABLE_01' || 'table is created.');
DBMS_OUTPUT.PUT_LINE('SAMPLE_TABLE_01' || 'table already exists.');
In SQL developer, if SAMPLE_TABLE_01 exists, i get the message 'table already exists' or otherwise. However in SQL PLUS, I receive oracle exception ORA-00955: name is already used by an existing object
It does not evaluate for IF and does not go in the DBMS_OUTPUT line. Please help.
Please try ... WHERE UPPER(table_name) = 'SAMPLE_TABLE_01';
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Tags for this Thread
Click Here to Expand Forum to Full Width