9 Replies Latest reply: Jan 2, 2013 3:28 AM by Ora RSS

    How to identify the constraint error

    959687
      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
          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
            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
                  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
                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
                  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
                    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
                      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
                        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
                          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.