This discussion is archived
9 Replies Latest reply: Jan 2, 2013 1:28 AM by Ora RSS

How to identify the constraint error

959687 Newbie
Currently Being Moderated
Hi friends, i got constraint error, like this
{4,209,669     123112 09:27:44:Code :ORA-02290: check constraint (OPS$hgn.CS_MQ_BADNORETAIL_CT7) violated at nopMASTERat sub exec block in pop_je2acct_othr}

How can i identify, the from which procedure or package its failing... kindly help me out ..
thanks in adavance .
  • 1. Re: How to identify the constraint error
    Marwim Expert
    Currently Being Moderated
    Hello,
    i got constraint error, like this
    When the message is in a log file: are there other entries before that can tell you what the program has done before?

    Is this the complete message? When the error message gives you no line number or additional information then it is an evidence for poor exception handling. Most likely only SQLERRM is logged instead of an additional package/procedure name or dbms_utility.format_error_stack / dbms_utility.format_error_backtrace.

    Can you give us additional informations?

    Regards
    Marcus
  • 2. Re: How to identify the constraint error
    yoonas Expert
    Currently Being Moderated
    Hi ,

    Catch the exception in your procdure and when you handle the exception include the procedure name in error message
      declare
      ex_check_voilation exception;
      pragma exception_init (ex_check_voilation, -2290);
    begin
      insert into ..............
    exception
      when ex_check_voilation then
            
        dbms_output.put_line(sqlerrm || 'error at procedure' || 'procdure_name');
      --  raise_application_error(-20001,sqlerrm || 'your own error message');
       
    end;
  • 3. Re: How to identify the constraint error
    Marwim Expert
    Currently Being Moderated
        dbms_output.put_line(sqlerrm || 'error at procedure' || 'procdure_name');
      --  raise_application_error(-20001,sqlerrm || 'your own error message');
    Exception handling/logging should not depend on dbms_output.
    And more important: the raise_application_error must not be commented ;-)

    Regards
    Marcus
  • 4. Re: How to identify the constraint error
    959687 Newbie
    Currently Being Moderated
    yes, i got error line no also
    (Line Number 4,209,670     123112 09:27:44:Code :ORA-02290: check constraint (PS$CMS.CS_JEQ_BADNORETAIL_CT7) violated at nhpMASTERat sub exec block in pop_je2acct_othr
    Line Number 4,209,669     123112 09:27:44:Code :ORA-02290: check constraint (OPS$CMS.CS_JEQ_BADNORETAIL_CT7) violated at nhPMASTERat sub exec block in pop_je2acct_othr
    Summary
    File     Time     Error     Count
    xoplog.20121231     123112 09:20     Code :ORA-02290: check constraint (OPS$CMS.CS_JEMQ_BADNORETAIL_CT7) violated at XOPMASTERat sub exec block in      2}

    And my package is already running in production, and i got this error from prodcution database..but i dont no from which package or procedure, it means ..plz guide me
  • 5. Re: How to identify the constraint error
    959687 Newbie
    Currently Being Moderated
    Hi friend what i have to mention in the insert into statement in the below code, i..e. error message or table name
    {declare
    ex_check_voilation exception;
    pragma exception_init (ex_check_voilation, -2290);
    begin
    insert into .............. (here)
    exception
    when ex_check_voilation then

    dbms_output.put_line(sqlerrm || 'error at procedure' || 'procdure_name');
    -- raise_application_error(-20001,sqlerrm || 'your own error message');

    end;
    }
  • 6. Re: How to identify the constraint error
    yoonas Expert
    Currently Being Moderated
    You are right Marcus.

    I should have been little more clear, i just want to show op if it is handled this way you will come to know which package is causing the error.
  • 7. Re: How to identify the constraint error
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    is these are table names or anything else..
    pop_je2acct_othr  and
    nhpMASTERat or nhpMASTER
  • 8. Re: How to identify the constraint error
    Marwim Expert
    Currently Being Moderated
    This surely is not the default Oracle message. it would look like
    DECLARE
        n NUMBER;
    BEGIN
        n := 1/0;
    END;
    /
    
    DECLARE
    *
    ERROR at line 4:
    ORA-01476: divisor is equal to zero
    ORA-06512: at line 4
    or
    DECLARE
        n NUMBER;
    BEGIN
        n := 1/0;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line(
                 dbms_utility.format_error_stack()||CHR(10)||
                 dbms_utility.format_error_backtrace()
                 );
            RAISE;
    END;
    /
    
    ORA-01476: divisor is equal to zero
    
    ORA-06512: at line 4
    So you have to look into your packages and find out how your log messages are built up.
    A hint might be nhpMASTERat and pop_je2acct_othr. You can search your code where these names occur:
    SELECT  owner
           ,name
           ,type
           ,line
           ,text
    FROM    sys.all_source
    WHERE   owner = user
    AND     (  INSTR(UPPER(text),UPPER('nhpMASTERat')) > 0
            OR INSTR(UPPER(text),UPPER('pop_je2acct_othr')) > 0
            )
    ORDER BY owner
            ,name
            ,type
            ,line
    Marcus
  • 9. Re: How to identify the constraint error
    Ora Pro
    Currently Being Moderated
    You can see in ALL_CONS_COLUMNS with which table your CHECK constraint is associated.
    This check contraint exception occur while you are inserting data.
    Check which package/function/procedure is inserting data into that table.
    IMO, it can be a data issue.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points