Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unanswered: ORA-00001: unique constraint (SYS.I_IDL_CHAR1) violated

    I am attempting to compile a stored procedure. If I attempt to reference the table sys_divisions_to_companies shown below I get the following error:

    ORA-00604: error occurred at recursive SQL level 1
    ORA-00001: unique constraint (SYS.I_IDL_CHAR1) violated

    cursor pGetDivision(pDivisionId VARCHAR2) is
    select * from sys_divisions_to_companies where division_id = pDivisionId;

    SYS_DIVISIONS_TO_COMPANIES is just a two column table. here is the DDL and constraints on the table:


    CREATE TABLE SYS_DIVISIONS_TO_COMPANIES (
    DIVISION_ID NUMBER (14) NOT NULL,
    COMPANY_ID NUMBER (14) NOT NULL,
    CONSTRAINT UNIQUE_COMP_DIV
    UNIQUE (DIVISION_ID, COMPANY_ID))
    TABLESPACE LOADS_SYS_USER_DATA
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 24576
    NEXT 8192
    PCTINCREASE 1
    MINEXTENTS 1
    MAXEXTENTS 121
    FREELISTS 1 FREELIST GROUPS 1 )
    NOCACHE;


    CREATE INDEX LOADS.IDXDIVTOCOMPANY ON
    "LOADS".SYS_DIVISIONS_TO_COMPANIES(DIVISION_ID , COMPANY_ID)
    TABLESPACE LOADS_INDICES_SMALL PCTFREE 10 STORAGE(INITIAL 57344 NEXT 131072 PCTINCREASE 0 )
    ;


    pDivisionId is just an IN parameter to the stored procedure. The sys_divisions_to_companies table is what seems to be causing the problem. If I run the query shown above by itself outside of the stored procedure, everything works fine.

    I've checked the data dictionary size and have dropped and recreated the stored procedure. The only thing I have not done is drop the table and recreate its indexes.

    I haven't found any solution to this problem so far. Please help.

    Thanks,
    Rai

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: ORA-00001: unique constraint (SYS.I_IDL_CHAR1) violated

    Originally posted by rais
    I am attempting to compile a stored procedure. If I attempt to reference the table sys_divisions_to_companies shown below I get the following error:

    ORA-00604: error occurred at recursive SQL level 1
    ORA-00001: unique constraint (SYS.I_IDL_CHAR1) violated

    cursor pGetDivision(pDivisionId VARCHAR2) is
    select * from sys_divisions_to_companies where division_id = pDivisionId;

    SYS_DIVISIONS_TO_COMPANIES is just a two column table. here is the DDL and constraints on the table:


    CREATE TABLE SYS_DIVISIONS_TO_COMPANIES (
    DIVISION_ID NUMBER (14) NOT NULL,
    COMPANY_ID NUMBER (14) NOT NULL,
    CONSTRAINT UNIQUE_COMP_DIV
    UNIQUE (DIVISION_ID, COMPANY_ID))
    TABLESPACE LOADS_SYS_USER_DATA
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 24576
    NEXT 8192
    PCTINCREASE 1
    MINEXTENTS 1
    MAXEXTENTS 121
    FREELISTS 1 FREELIST GROUPS 1 )
    NOCACHE;


    CREATE INDEX LOADS.IDXDIVTOCOMPANY ON
    "LOADS".SYS_DIVISIONS_TO_COMPANIES(DIVISION_ID , COMPANY_ID)
    TABLESPACE LOADS_INDICES_SMALL PCTFREE 10 STORAGE(INITIAL 57344 NEXT 131072 PCTINCREASE 0 )
    ;


    pDivisionId is just an IN parameter to the stored procedure. The sys_divisions_to_companies table is what seems to be causing the problem. If I run the query shown above by itself outside of the stored procedure, everything works fine.

    I've checked the data dictionary size and have dropped and recreated the stored procedure. The only thing I have not done is drop the table and recreate its indexes.

    I haven't found any solution to this problem so far. Please help.

    Thanks,
    Rai
    This happens when you do the CREATE OR REPLACE command, not when you run the procedure? It is complaining about the violation of a unique constraint I_IDL_CHAR1 owned by SYS. Sounds like an internal Oracle problem and you would need to call Oracle support.

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    I have a solution. It wasn't really so obvious.

    The table "LOADS".SYS_DIVISIONS_TO_COMPANIES had select privileges on it to the schema called "BATCHRPT". A public synonym exists for this table, but to refer to it within the "BATCHRPT" schema, it must be prefixed with the owner like:

    cursor pGetDivision(pDivisionId VARCHAR2) is
    select * from LOADS.sys_divisions_to_companies where division_id = pDivisionId;

    It's strange, because when you execute the query outside of a stored procedure without prefixing the table with the schema name, everything works.

    rai

  4. #4
    Join Date
    Apr 2003
    Posts
    3

    Smile Re: ORA-00001: unique constraint (SYS.I_IDL_CHAR1) violated

    Originally posted by andrewst

    This happens when you do the CREATE OR REPLACE command, not when you run the procedure?

    Yes, this happens when the procedure is being created or replaced.

    It is complaining about the violation of a unique constraint I_IDL_CHAR1 owned by SYS. Sounds like an internal Oracle problem and you would need to call Oracle support.

    I guess the error message should be a little more descriptive too.
    BTW, thanks for your response

    Regards,
    Rai

Posting Permissions

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