#1
  1. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,883
    Rep Power
    891

    Question Resolved: Create table script is generating ORA-00955 in SQL+ but not TOAD


    **** RESOLVED SEE SECOND POST ****


    I have this

    Code:
    BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE CP.CP_PC_SOR';
    EXCEPTION
       WHEN OTHERS THEN
          IF SQLCODE != -942 THEN
             RAISE;
          END IF;
    END;
    
    /
    
    CREATE TABLE CP.CP_PC_SOR (
        SOR_ID        Varchar2(10) not null,
        SOR_NAME    Varchar2(50) not null,
        DEFINITION    Varchar2(1024),
        CONTACTS    Varchar2(1024),    
        BUSINESS_GROUP    Varchar2(1024),    
        LAST_MODIFIED_BY Varchar2(25) not null,    
        LAST_MODIFIED    Date not null,    
        CONSTRAINT pk_CP_PC_SOR PRIMARY KEY (SOR_ID)
    );
    
    /
    
    ALTER TABLE CP.CP_PC_SOR MODIFY (LAST_MODIFIED default sysdate);
    ALTER TABLE CP.CP_PC_SOR MODIFY (LAST_MODIFIED_BY default user);
    GRANT ALL PRIVILEGES ON CP.CP_PC_SOR TO CP_APP;
    
    /
    It runs fine in TOAD as an executed script

    However SQL+ which my DBA needs to use in higher environments gives this output

    Code:
    PL/SQL procedure successfully completed.
    
    
    Table created.
    
    CREATE TABLE CP.CP_PC_SOR (
                    *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object
    
    
    
    Table altered.
    
    
    Table altered.
    
    
    Grant succeeded.
    
    
    Grant succeeded.
    It actually seems to create the table ok, and seems to give the error after it creates the table (the Table Created message displays then the error!?) the indexes and keys and constraints seem ok too. Gives the same error for other tables too, even with the explicit drop procedure.

    I verified no other objects have the name with SELECT * FROM all_objects WHERE object_name like 'CP_PC%'

    I am baffled someone please point out the stupidly silly error? Thank you.

    Running Oracle 11g (11.2.0.3.0) and SQL+ from 11g Client
    Last edited by medialint; January 12th, 2015 at 06:24 PM. Reason: Resolved
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,883
    Rep Power
    891
    Resolved:

    The slash commit after the create was not only unnecessary but actually caused this error.

    Code:
    BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE CP.CP_PC_SOR';
    EXCEPTION
       WHEN OTHERS THEN
          IF SQLCODE != -942 THEN
             RAISE;
          END IF;
    END;
    
    /
    
    CREATE TABLE CP.CP_PC_SOR (
        SOR_ID        Varchar2(10) not null,
        SOR_NAME    Varchar2(50) not null,
        DEFINITION    Varchar2(1024),
        CONTACTS    Varchar2(1024),    
        BUSINESS_GROUP    Varchar2(1024),    
        LAST_MODIFIED_BY Varchar2(25) not null,    
        LAST_MODIFIED    Date not null,    
        CONSTRAINT pk_CP_PC_SOR PRIMARY KEY (SOR_ID)
    );
    
    
    -- This commit slash should not be here
    / 
    
    ALTER TABLE CP.CP_PC_SOR MODIFY (LAST_MODIFIED default sysdate);
    ALTER TABLE CP.CP_PC_SOR MODIFY (LAST_MODIFIED_BY default user);
    GRANT ALL PRIVILEGES ON CP.CP_PC_SOR TO CP_APP;
    
    /
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

IMN logo majestic logo threadwatch logo seochat tools logo