DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Hybrid View

  1. #1
    Join Date
    Nov 2013

    Post SQL works fine in SQL developer, throws ORACLE exception in SQL PLus

    Hi All

    cnt number;
    SELECT count(*) INTO cnt
    FROM user_tables
    WHERE table_name = 'SAMPLE_TABLE_01';
    IF cnt = 0 THEN
    execute immediate 'CREATE TABLE SAMPLE_TABLE_01
    sample_column01 DATE,
    sample_column02 VARCHAR2 (128),
    sample_column03 VARCHAR2 (255) NOT NULL,
    sample_column04 BLOB,
    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.');
    END IF;

    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.

  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    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

Posting Permissions

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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.