1 2 Previous Next 15 Replies Latest reply on Jul 9, 2016 1:03 PM by 848964

    Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain

    848964

      Hi,

      I have a form requirement as below where one of the validation is - for same combination of Operation, Comp stream, product code  and end date being null for both, the error message to be displayed as below and raise trigger failure.

       

      form.jpg

       

      But this error is actually not retained as soon as I click on OK and also allows me to save the record without any issue. My expected resulted is to stop completely once that error is encountered.

       

      The code which I have written is :

      code.jpg

      Just note that here we are handling a scenario where 1 row is already created in table and while second is going to be created then the comparison is between previous saved data plus current unsaved data.

      Also if you could assist in a scenario where user enters first record and then enter second record. Those are not saved. Howz that to be handled.

        • 1. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
          Kalpataru

          Hi 848964,

          What is your forms version ?

          Which trigger you have used ?

          Post the trigger name.

          Put another condition in your query that is

          where your_table_column_name = :your_block_name.item_name_of (depth_structure)

          In the item where your value 50130 and 30220 are displayed.

           

           

          Hope it will solve your issue

          • 2. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
            Zlatko Sirotic

            > My expected resulted is to stop completely once that error is encountered.

            Do not use this:

             

            EXCEPTION

              WHEN NO_DATA_FOUND THEN NULL;

             

            > Also if you could assist in a scenario where user enters first record and then enter second record. Those are not saved. Howz that to be handled.

            You can use validation in PRE-INSERT Forms trigger, or database-side, in database triggers.

             

            Regards,

            Zlatko

            • 3. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
              848964

              Hi, Thanks for your reply.Regarding the RAISE FORM TRIGGER FAILURE, I have already removed the  exception section when other the null as that was the causing the issue as even the trigger failure was happening but this was flowing to the "THEN NULL" section so ultimately the error dialog was not getting retained. Now since I have removed it, it is working fine.

               

              Regarding another issue I mentioned, my requirement is something like this

               

              1) User enters the 1st set of record and saves in table

              2) User enters 2nd record with same combination. (I already have a WHEN-VALIDATE-RECORD trigger on block which checks the value currently being entered by user and compares with data saved in table and shows message like - Duplicate combination exists". This is simple because I have the option to check in some table using a SQL query inside the trigger

               

              3) The scenario which I am now talking about is say, the user enters 1 set of record and without saving it he also start entering 2nd record and both have same combination. I need to throw an error of duplicate combination now. I just need to find a way for this in this case data is not saved in any table so sql comparison is not possible.

              In what way I can put a check while the user enters both records with same combinations and can show as error.

               

              Regards

              Ad

              • 4. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                Kalpataru

                I have already give you in my previous post to use another condition in your where clause.

                Have you checked that ?

                • 5. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                  Zlatko Sirotic

                  I understand your request.

                  In my opinion, a better way is one that I wrote above:

                  - validation in the PRE-INSERT Forms trigger (before INSERT-ing the second row, the first one is already in the database),

                  - or in the database triggers.

                   

                  There are another ways, eg:

                  - "loop" through the rows in the block,

                  - or Craig's method.

                   

                  See eg:

                  Re: which trigger to use  to find duplicate values when enter

                   

                  Regards,

                  Zlatko

                  • 6. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                    Andreas Weiden

                    Simply remove the WHEN OTHERS THEN NULL; part from your code. It catches the FORM_TRIGGER_FAILURE and makes it useless.

                     

                    And another remark: if the data should be unique, there should also be a unique-key-constraint on your db-table to prohibit saving.

                    • 7. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                      848964

                      Hi Zlatko,

                       

                      I have used the looping logic for traversing through the previous records and checking. This I ad written in KEY_NEXT_ITEM as thats the trigger where we can write traversing related logic from one record to other. So this is now working fine for me. Thanks.

                       

                      Now during this logic, I need to display some error message in oracle form for which I have written RAISE FORM_TRIGGER_FAILURE. The error message shows up correctly on the form but it doesn't stops there and it allows me to save record by bypassing the error. Could you please advise on this.I have attached the KEY_NEXT_ITEM code for your reference.

                       

                      I was researching on this issue and it seems to me that I cannot raise error in KEY_NEXT_ITEM. Is that correct. If yes which other trigger I can try or any alternatives. I am still analysing but incase you have any idea, appreciate if you could let me know the same.

                       

                      Thanks & Regards,

                      Ad

                      • 8. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                        848964

                        DECLARE

                         

                          i                       NUMBER;

                          l_end_date              DATE;

                          l_start_date            DATE;

                          l_operation             VARCHAR2(3);

                          l_component_stream      VARCHAR2(2);

                          l_component_stream_flag VARCHAR2(1);

                          l_product_code          VARCHAR2(3);

                          l_product_code_flag     VARCHAR2(1);

                        -- ex_stdate_err           EXCEPTION;

                         

                        BEGIN

                         

                          l_end_date               :=  NULL;

                          l_start_date             :=  NULL;

                          l_operation              :=  NULL;

                          l_component_stream       :=  NULL;

                          l_component_stream_flag  :=  NULL;

                          l_product_code           :=  NULL;      

                          l_product_code_flag      :=  NULL;

                         

                          IF :DRGL_ESPIRIT_DEPTH_STRUC.START_DATE IS NOT NULL

                          THEN

                         

                            BEGIN

                           

                                   SELECT DISTINCT :DRGL_ESPIRIT_DEPTH_STRUC.START_DATE-1

                                   INTO   l_end_date

                                  FROM   DRGL_ESPIRIT_DEPTH_STRUC_TAB DS_TAB

                                   WHERE  DS_TAB.OPERATION                      = :DRGL_ESPIRIT_DEPTH_STRUC.OPERATION

                                  AND    NVL(DS_TAB.COMPONENT_STREAM,'X')      = NVL(:DRGL_ESPIRIT_DEPTH_STRUC.COMPONENT_STREAM,'X')

                                  AND    NVL(DS_TAB.COMPONENT_STREAM_FLAG,'N') = NVL(:DRGL_ESPIRIT_DEPTH_STRUC.COMPONENT_STREAM_FLAG,'N')

                                  AND    NVL(DS_TAB.PRODUCT_CODE,'X')          = NVL(:DRGL_ESPIRIT_DEPTH_STRUC.PRODUCT_CODE,'X')

                                  AND    NVL(DS_TAB.PRODUCT_CODE_FLAG,'N')     = NVL(:DRGL_ESPIRIT_DEPTH_STRUC.PRODUCT_CODE_FLAG,'N')

                                   --AND    DS_TAB.START_DATE                     < :DRGL_ESPIRIT_DEPTH_STRUC.START_DATE

                                   AND    DS_TAB.DEPTH_STRUCTURE               <> :DRGL_ESPIRIT_DEPTH_STRUC.DEPTH_STRUCTURE

                                   AND    DS_TAB.END_DATE IS NULL;

                                  

                                   l_start_date             := :DRGL_ESPIRIT_DEPTH_STRUC.START_DATE;

                                   l_operation              := :DRGL_ESPIRIT_DEPTH_STRUC.OPERATION;

                                   l_component_stream       := :DRGL_ESPIRIT_DEPTH_STRUC.COMPONENT_STREAM;

                                   l_component_stream_flag  := :DRGL_ESPIRIT_DEPTH_STRUC.COMPONENT_STREAM_FLAG;

                                   l_product_code           := :DRGL_ESPIRIT_DEPTH_STRUC.PRODUCT_CODE;      

                                   l_product_code_flag      := :DRGL_ESPIRIT_DEPTH_STRUC.PRODUCT_CODE_FLAG;        

                                   i                        := :SYSTEM.CURSOR_RECORD;    

                                                      

                                   go_record(i-1); 

                                   LOOP

                                  

                                    IF :DRGL_ESPIRIT_DEPTH_STRUC.OPERATION                          = l_operation

                                    AND NVL(:DRGL_ESPIRIT_DEPTH_STRUC.COMPONENT_STREAM,'X')       = NVL(l_component_stream,'X')

                                    AND NVL(:DRGL_ESPIRIT_DEPTH_STRUC.COMPONENT_STREAM_FLAG,'N')  = NVL(l_component_stream_flag,'N')

                                    AND NVL(:DRGL_ESPIRIT_DEPTH_STRUC.PRODUCT_CODE,'X')           = NVL(l_product_code,'X')

                                    AND NVL(:DRGL_ESPIRIT_DEPTH_STRUC.PRODUCT_CODE_FLAG,'N')      = NVL(l_product_code_flag,'N')

                                  

                                    THEN

                                    ---

                                  

                                    IF l_start_date <= :DRGL_ESPIRIT_DEPTH_STRUC.START_DATE

                                    THEN

                                              

                                       FND_MESSAGE.SET_STRING('Error: Current Record Start Date should be greater than previous record Start Date');

                                            FND_MESSAGE.ERROR;

                                            RAISE FORM_TRIGGER_FAILURE;         

                                      

                                    ELSE

                                    IF

                                      :DRGL_ESPIRIT_DEPTH_STRUC.END_DATE IS NULL

                                      THEN

                                           :DRGL_ESPIRIT_DEPTH_STRUC.END_DATE := l_end_date;

                                    ELSE         

                                      NULL;

                                    

                                    END IF;

                         

                         

                                    END IF;

                                

                                     -----------

                                    /* IF

                                      :DRGL_ESPIRIT_DEPTH_STRUC.END_DATE IS NULL

                                      THEN

                                           :DRGL_ESPIRIT_DEPTH_STRUC.END_DATE := l_end_date;

                                     ELSE

                                      NULL;

                                     END IF; */

                                     ---------

                                    

                                     EXIT;

                                    

                                    END IF; 

                                  

                                    PREVIOUS_RECORD;

                                  

                                   END LOOP;

                                    

                                   go_record(i);

                                   go_item('DRGL_ESPIRIT_DEPTH_STRUC.END_DATE'); 

                                   

                            EXCEPTION

                          

                            WHEN form_trigger_failure Then

                                RAISE;

                            /*WHEN ex_stdate_err

                            THEN

                               FND_MESSAGE.SET_STRING('Error: Current Record Start Date should be greater than previous record Start Date');

                                    FND_MESSAGE.ERROR;

                                    RAISE FORM_TRIGGER_FAILURE; */           

                         

                            WHEN OTHERS

                            THEN NULL;

                          

                            END;

                         

                         

                          END IF;

                         

                          EXCEPTION

                          

                            WHEN form_trigger_failure Then

                                RAISE;

                               

                                WHEN OTHERS

                            THEN NULL;

                         

                         

                        END;

                         

                         

                        go_item('DRGL_ESPIRIT_DEPTH_STRUC.END_DATE');

                        • 9. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                          Zlatko Sirotic

                          Do not use this:

                           

                          BEGIN

                             ...

                             RAISE FORM_TRIGGER_FAILURE;

                             ...

                          EXCEPTION

                             WHEN FORM_TRIGGER_FAILURE THEN

                                RAISE;   

                             WHEN OTHERS THEN

                                NULL;

                          END;

                           

                          Simply, use this:

                           

                          BEGIN

                             ...

                             RAISE FORM_TRIGGER_FAILURE;

                             ...

                          END;

                           

                          > I was researching on this issue and it seems to me that I cannot raise error in KEY_NEXT_ITEM. Is that correct.

                          No, you can raise error in KEY-NEXT-ITEM trigger.

                           

                          > ... which other trigger I can try or any alternatives.

                          I use a WHEN-NEW-ITEM-INSTANCE trigger.

                          Unfortunately, we can not use WHEN-VALIDATE-ITEM trigger, because:

                             Legal Commands: SELECT statements, unrestricted built-ins

                          Unlike WHEN-VALIDATE-ITEM, KEY-NEXT-ITEM and WHEN-NEW-ITEM-INSTANCE permit unrestricted built-ins:

                             Legal Commands: SELECT statements, restricted built-ins, unrestricted built-ins.

                           

                           

                          Regards,

                          Zlatko

                          • 10. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                            848964

                            But then how can I handle other exceptions, in case anything else causes exceptions other than the form trigger failure..as I have more than 1 BEGIN..END block

                            • 11. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                              Zlatko Sirotic

                              "Handling" exceptions with

                               

                              EXCEPTION

                                 WHEN OTHERS THEN

                                    NULL;

                              END;

                               

                              is very bad way.

                              In (at least) 99.99% of the cases, this is a mistake.

                               

                              Regards,

                              Zlatko

                              • 12. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                                Andreas Weiden

                                If you put a NULL in your exception-handler, you don't handle the exception, you simply hide it. And thats very bad as you will never know if something goes wrong. If something unexpected happens its better to see the error-message and can react on it.

                                • 13. Re: Oracle form error using RAISE FORM_TRIGGER_FAILURE is bypassed and data is getting saved without any error.Need the error to retain
                                  848964

                                  Agreed. Just for now, I have removed the exceptions just to if any other error comes up or not.

                                   

                                  1 2 Previous Next