10 Replies Latest reply: Dec 31, 2012 3:01 AM by 920719 RSS

    How to Catch Database exception in form

    920719
      Hi Friends,
      I have a table which has a unique constraint on couple of columns. I have created a form on this table. When I enter two similar items in those columns then the unique constraint should fire. This seems to happen as I am not able to save the form. But this exception is thrown by the database during the time of commit. How can I catch this exception. I have a function in the forms that does all the validation and at the end it issues a COMMIT_FORM.

      Should I catch this in any trigger. Any hints please

      Thanks
        • 1. Re: How to Catch Database exception in form
          Sachinmrt
          Use Pre-Insert Trigger on Block level.
          • 2. Re: How to Catch Database exception in form
            920719
            Hi Sachin,
            The unique constraint violation is thrown in the sql when the commit happens. How can I do this in the Pre-commit. Any idea.

            Thanks
            • 3. Re: How to Catch Database exception in form
              920719
              Hi Sachin,
              Basically I have a unique constraint created with deferrable initially deferred NOVALIDATE option. When I save the duplicate records from forms the records are not getting saved. I am presuming this is because of the unique constraint. But I need to catch this and then display to the user that there are duplicate lines. Instead my code is displaying the record successfully saved message inspite of this error.

              How can I achieve this.

              Thanks
              • 4. Re: How to Catch Database exception in form
                Sachinmrt
                If your error really come due to unique constraint then use this code on ON-ERROR trigger


                DECLARE
                ERRNUM NUMBER := ERROR_CODE;
                BEGIN
                IF ERRNUM = 40508 THEN
                MESSAGE('Unable to save ! Please Check ..');
                MESSAGE('Unable to save ! Please Check ..');
                RAISE FORM_TRIGGER_FAILURE;
                END IF;
                END;
                • 5. Re: How to Catch Database exception in form
                  kikolus
                  Hi,
                  Try use this code within ON-ERROR trigger (form level):
                   
                  declare 
                  bemuse integer := bums_error_code; 
                  eco integer := error_code; 
                  begin 
                  --Unable to insert/unable record or catch unhanded exception (e.g.from trigger). 
                  if eco in (40508,40509,40735) then 
                    if bemuse = -1 then -- unique constraint violated 
                      case :systematize -- if you have multiple blocks with different unique keys  you can distinguish them here 
                      when 'BLOCK' then 
                        -- do something 
                       massage('Unable to save record due to unique constraint violation); 
                      else 
                        null; 
                      end case; 
                    end if; 
                  end if; 
                  end;                  
                  • 6. Re: How to Catch Database exception in form
                    yoonas
                    Hi,

                    use 'dup_val_on_index' predefined exception
                    declare
                    
                    begin
                    ........
                    exception
                    when dup_val_on_index then
                     --do here your stuff
                    end; 
                    • 7. Re: How to Catch Database exception in form
                      920719
                      Hi All,
                      The ON-ERROR is not working either. I have created the unique constraint like this

                      ALTER TABLE xxhr_sub add constraint xxhr_unique_sub unique(emp_num, bg_id) deferrable initially deferred NOVALIDATE;

                      Does this make any difference. I am not able to catch the exception in POST-FORMS-COMMIT or POST-DATABASE-COMMIT either.

                      I have a procedure which does the validations on the data to the form. Once the validations to the data are successful then I issue a COMMIT-FORM in the same procedure at the end.

                      Do I have to change this.

                      Thanks
                      • 8. Re: How to Catch Database exception in form
                        yoonas
                        In the same procedure write an exception
                        exception
                        when dup_val_on_index then
                         --do here your stuff
                        end;
                        • 9. Re: How to Catch Database exception in form
                          Andreas Weiden
                          As you have a deferres constraint, this may have to be caught in an POST-FORMS-COMMIT or a POST-DATABASE-COMMIT-trigger. I remember there was some specialty about deferred constraints in forms, but i can't remember exactly what it was.
                          • 10. Re: How to Catch Database exception in form
                            920719
                            Hi
                            I could not catch the exception. However the POST-DATABASE-COMMIT trigger will be called if it commits successfully otherwise this will not be called. I set a global variable in the POST-DATABASE-COMMIT which helped me resove this issue.

                            Thanks for different insights. Having tried the above things I arrived at the solution.

                            Thanks