1 2 Previous Next 17 Replies Latest reply: Apr 25, 2013 1:01 PM by user626836 RSS

    Unhandled Exception Forms 6i

    user626836
      Hi everyone,
      I have created a form using Forms 6i and I added a button at the end of the form to bring values into 4 columns from another table to make a formula and then make a calculation between all these columns. Right now only one of these columns have values inside and the others are empty, but when I click on the button I receive the following error: FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01403
      Is there a way to avoid this?
      This is the code that I have in my button:
      Begin
           FIRST_RECORD;
           LOOP
           SELECT CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4 INTO
           :CREDITO_UNO, :CREDITO_DOS, :CREDITO_TRES, :CREDITO_CUATRO
           FROM MY_TABLE
           WHERE SCHOOL_ID   =:SCHOOL_ID  AND
                 CLASS_ID    =:CLASS_ID   AND
                 TEACHER_ID  =:TEACHER_ID AND
                 SCHOOL_YEAR =:PERIOD;
                EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
                NEXT_RECORD;
           END LOOP;
      End;
        • 1. Re: Unhandled Exception Forms 6i
          InoL
          A "select ... into ..." should always return a value. If not, you get ORA-01403.
          So, either your select statement is incorrect (because you know that you should always get a value), or you should have an exception handler for the when no_data_found error.
          • 2. Re: Unhandled Exception Forms 6i
            user626836
            Hi Inol and thanks for your quick reply,
            Is there another way to bring the data without using the into command?
            • 3. Re: Unhandled Exception Forms 6i
              InoL
              Is there another way to bring the data without using the into command?
              for r in (select ...)
              loop
                :credito_uno := r.credit_value;
                ...
              end loop;
              • 4. Re: Unhandled Exception Forms 6i
                user626836
                Hi Inol,
                I have try the code you sent me and also try a cursor, but is only displaying values in my first row.
                These are the codes used:
                1)
                Begin
                     For C in
                     (Select CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4
                       FROM MY_TABLE
                       WHERE SCHOOL_ID   =:SCHOOL_ID  AND
                             CLASS_ID    =:CLASS_ID   AND
                             TEACHER_ID  =:TEACHER_ID AND
                             SCHOOL_YEAR =:PERIOD)
                             Loop
                                  :CREDITO_UNO    := C.CREDIT_VALUE;
                                     :CREDITO_DOS    := C.CREDIT_VALUE_Q2;
                                     :CREDITO_TRES   := C.CREDIT_VALUE_Q3;
                                     :CREDITO_CUATRO := C.CREDIT_VALUE_Q4;
                             End Loop;
                End;
                2)
                Begin
                     Declare
                          Cursor C1 is
                          Select CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4
                       FROM MY_TABLE
                       WHERE SCHOOL_ID   =:SCHOOL_ID  AND
                             CLASS_ID    =:CLASS_ID   AND
                             TEACHER_ID  =:TEACHER_ID AND
                             SCHOOL_YEAR =:PERIOD;
                     Begin
                          For C in C1
                          Loop
                               :CREDITO_UNO    := C.CREDIT_VALUE;
                               :CREDITO_DOS    := C.CREDIT_VALUE_Q2;
                               :CREDITO_TRES   := C.CREDIT_VALUE_Q3;
                               :CREDITO_CUATRO := C.CREDIT_VALUE_Q4;
                                    next_record;
                          End Loop;
                     
                     End;
                End;
                • 5. Re: Unhandled Exception Forms 6i
                  InoL
                  Don't forget your previous code:
                       FIRST_RECORD;
                       LOOP
                                  (...)
                            EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
                            NEXT_RECORD;
                       END LOOP;
                  That's a different loop!
                  • 6. Re: Unhandled Exception Forms 6iHi
                    Amatu Allah Neveen Ebrahim
                    Hi
                    u should use..
                    NEXT_RECORD;
                    In ur first example inside cursor loop
                    U should also exit the loop this is infinite loop.
                    Just for ur information the select into is returning only one value but the cursor select a number of rows depending on where conditions..
                    So u have to do the assign statement inside the loop not the select into

                    Amatu Allah
                    • 7. Re: Unhandled Exception Forms 6i
                      user626836
                      I'm a little confuse now.
                      This what I did now, but it still displaying a value only in my first row.
                      Begin
                           For C in
                           (Select CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4
                             FROM MY_TABLE
                             WHERE SCHOOL_ID   =:SCHOOL_ID  AND
                                   CLASS_ID    =:CLASS_ID   AND
                                   TEACHER_ID  =:TEACHER_ID AND
                                   SCHOOL_YEAR =:PERIOD)
                                   
                                   Loop
                                        :CREDITO_UNO    := C.CREDIT_VALUE;
                                           :CREDITO_DOS    := C.CREDIT_VALUE_Q2;
                                           :CREDITO_TRES   := C.CREDIT_VALUE_Q3;
                                           :CREDITO_CUATRO := C.CREDIT_VALUE_Q4;
                                           Exit When :SYSTEM.LAST_RECORD = 'TRUE';
                                           Next_record;
                                   End Loop;
                      End;
                      • 8. Re: Unhandled Exception Forms 6i
                        InoL
                        Take a good look at your code a understand what is happening. There is a loop to go through all the records, and a loop to fetch the data from the table.
                        The 2nd loop (to get data from the table) is not really necessary. It is, more or less, the same as your "select .. into ...". Your question was that you didn't want to use the "select ... into ...". I just gave an example to replace that part only.
                        • 9. Re: Unhandled Exception Forms 6i
                          user626836
                          Ok, if I decide to do it like this:
                          BEGIN
                          FIRST_RECORD;
                          LOOP
                          Select CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4
                                 FROM MY_TABLE
                                 WHERE SCHOOL_ID   =:SCHOOL_ID  AND
                                       CLASS_ID    =:CLASS_ID   AND
                                       TEACHER_ID  =:TEACHER_ID AND
                                       SCHOOL_YEAR =:PERIOD
                          EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'
                          NEXT_RECORD;
                          END LOOP;
                          END;
                          How can I fetch the values into each item?
                          • 10. Re: Unhandled Exception Forms 6i
                            O.Developer
                            HI,

                            Whenever you use select statement in PL/SQL, per my experience, three exceptions are must.

                            WHen No Data Found
                            When Too Many Rows
                            When Others

                            Secondly, did your sql code is getting value when you run only the select statement on SQL. Of course for each exception you put related message
                            Try this you will identify the issue very easily.

                            user626836 wrote:
                            Hi everyone,
                            I have created a form using Forms 6i and I added a button at the end of the form to bring values into 4 columns from another table to make a formula and then make a calculation between all these columns. Right now only one of these columns have values inside and the others are empty, but when I click on the button I receive the following error: FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01403
                            Is there a way to avoid this?
                            This is the code that I have in my button:
                            Begin
                                 FIRST_RECORD;
                                 LOOP
                                 SELECT CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4 INTO
                                 :CREDITO_UNO, :CREDITO_DOS, :CREDITO_TRES, :CREDITO_CUATRO
                                 FROM MY_TABLE
                                 WHERE SCHOOL_ID   =:SCHOOL_ID  AND
                                       CLASS_ID    =:CLASS_ID   AND
                                       TEACHER_ID  =:TEACHER_ID AND
                                       SCHOOL_YEAR =:PERIOD;
                                      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
                                      NEXT_RECORD;
                                 END LOOP;
                            End;
                            • 11. Re: Unhandled Exception Forms 6i
                              user346369
                              user626836 wrote:
                              Ok, if I decide to do it like this:
                              BEGIN
                              FIRST_RECORD;
                              LOOP
                              Select ... FROM MY_TABLE
                              EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'
                              NEXT_RECORD;
                              END LOOP;
                              END;
                              How can I fetch the values into each item?
                              The above code WILL NOT work. The Select statement has no INTO clause. Using it like that ONLY works in two places:
                              1. SQL Plus (which then formats the output), and
                              2. with in a FOR loop, like you have written in earlier posts.

                              And then, you are using:
                              EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'
                              THAT exit statement should never be used when you are trying to populate rows in your block. It is only used when you already have data in the block in multiple rows, and you want to step through all the rows in some looping process.

                              There are two ways to populate data in a block in your form.
                              1. If the block is based on a table in the database, all you need is two statements:
                              Go_block('MY_BLOCK_NAME');
                              Execute_Query;
                              2. doing it manually with your own sql select (but then you can not use Forms automatic processing, and update those values in the block.)
                              Here is the manual method:
                              Go_block('MY_BLOCK_NAME');
                              Clear_block;
                              For C in (Select column1, column2, column3, etc
                                            from My_table
                                            where....
                                            order by...
                                           )
                              Loop
                                If :System.Record_Status <> 'NEW' then
                                  Next_Record;
                                End if;
                                :MyBlock.Col1 := C.column1;
                                :MyBlock.Col2 := C.column2;
                                etc.
                              End Loop;
                              First_Record;
                              Note that there is NO "Exit when..." in the loop. It continues until all rows are fetched from the For...Loop cursor.

                              You can also declare the cursor as you did in your "2)" example above. That example should work, if you first do the Go_block and Clear_block.
                              • 12. Re: Unhandled Exception Forms 6i
                                InoL
                                THAT exit statement should never be used when you are trying to populate rows in your block.
                                Steve,
                                From what I understand from the OP, the block is already populated, and only a few items are calculated after the rows are retrieved. In that case, it would work.

                                Of course, it is probably much easier to use a post-query trigger and be done with all this looping stuff B-)

                                Ino
                                • 13. Re: Unhandled Exception Forms 6i
                                  user626836
                                  Hi Steve and thanks for your reply,
                                  I have used your second method and now I'm retrieving more data into my items, but I realized that I was measing another table in the select statement, and I also forgot to mention that all the items that I have are database table except 4 of them which are the one that I want to display using the method you gave me. When I run the form I choose the year from the year item and then I press F8, so after I have all the values displayed on each database data item, then I press the button to display the 4 items which are not database item, but right now my form is only displaying those that has a value of 1.00, not those that has a value of 0.25 or 0.50
                                  This is the code:
                                  Begin
                                       For C in
                                       (Select CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4
                                         FROM MATERIAS_ISS A, NOTAS_ISS B
                                         WHERE A.SCHOOL_ID   =:SCHOOL_ID  AND
                                               A.CLASS_ID    =:CLASS_ID   AND
                                               A.TEACHER_ID  =:TEACHER_ID AND
                                               A.SCHOOL_YEAR =:SCHOOL_YEAR AND
                                               A.SCHOOL_ID = B.SCHOOL_ID AND
                                               A.CLASS_ID = B.CLASS_ID AND
                                               A.TEACHER_ID = B.TEACHER_ID)
                                               
                                               Loop
                                                    If :System.Record_Status <> 'NEW' then
                                                         Next_Record;
                                                    End If;
                                                    :CREDITO_UNO    := C.CREDIT_VALUE;
                                            :CREDITO_DOS    := C.CREDIT_VALUE_Q2;
                                            :CREDITO_TRES   := C.CREDIT_VALUE_Q3;
                                            :CREDITO_CUATRO := C.CREDIT_VALUE_Q4;
                                               End Loop;
                                               First_Record;
                                  End;
                                  • 14. Re: Unhandled Exception Forms 6i
                                    user346369
                                    InoL wrote:
                                    Steve,
                                    From what I understand from the OP, the block is already populated, and only a few items are calculated after the rows are retrieved. In that case, it would work.

                                    Of course, it is probably much easier to use a post-query trigger and be done with all this looping stuff B-)

                                    Ino
                                    Oh my word! You're right. I missed that point.

                                    626836 should use his original method in the first post, or as you wrote, in a post-query trigger.

                                    No matter where he selects those values, if he would enclose the select in a Begin-Exception-End, it would prevent the no_data_found errors.

                                    626836, using your method:
                                    BEGIN
                                      FIRST_RECORD;
                                      LOOP
                                        For C in
                                         ( Select CREDIT_VALUE, CREDIT_VALUE_Q2,
                                                     CREDIT_VALUE_Q3, CREDIT_VALUE_Q4
                                           FROM MATERIAS_ISS A, NOTAS_ISS B
                                           WHERE A.SCHOOL_ID   =:SCHOOL_ID
                                           AND   A.CLASS_ID    =:CLASS_ID
                                           AND   A.TEACHER_ID  =:TEACHER_ID
                                           AND   A.SCHOOL_YEAR =:SCHOOL_YEAR
                                           AND   A.SCHOOL_ID = B.SCHOOL_ID
                                           AND   A.CLASS_ID = B.CLASS_ID
                                           AND   A.TEACHER_ID = B.TEACHER_ID)
                                        Loop
                                          :CREDITO_UNO    := C.CREDIT_VALUE;
                                          :CREDITO_DOS    := C.CREDIT_VALUE_Q2;
                                          :CREDITO_TRES   := C.CREDIT_VALUE_Q3;
                                          :CREDITO_CUATRO := C.CREDIT_VALUE_Q4;
                                          -- always exit after populating the fields in the one row.
                                          Exit;
                                        End Loop;
                                        EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'
                                        NEXT_RECORD;
                                      END LOOP;
                                    END;
                                    Or this is less confusing. That inner loop above would be confusing to most people. You are only using the Loop structure to avoid select INTO. Besides, the code below takes fewer lines:
                                    BEGIN
                                      FIRST_RECORD;
                                      LOOP
                                        BEGIN
                                          Select CREDIT_VALUE, CREDIT_VALUE_Q2, CREDIT_VALUE_Q3, CREDIT_VALUE_Q4
                                            into :CREDITO_UNO, :CREDITO_DOS,    :CREDITO_TRES,   :CREDITO_CUATRO
                                            FROM MATERIAS_ISS A, NOTAS_ISS B
                                            WHERE A.SCHOOL_ID   = :SCHOOL_ID
                                              AND A.CLASS_ID    = :CLASS_ID
                                              AND A.TEACHER_ID  = :TEACHER_ID
                                              AND A.SCHOOL_YEAR = :SCHOOL_YEAR
                                              AND A.SCHOOL_ID   = B.SCHOOL_ID
                                              AND A.CLASS_ID    = B.CLASS_ID
                                              AND A.TEACHER_ID  = B.TEACHER_ID;
                                          Exception When No_data_found then null;
                                        END;
                                        EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE'
                                        NEXT_RECORD;
                                      END LOOP;
                                    END;
                                    Note that the Begin-Exception-End block of code could just have easily been placed in a post-query trigger, and it would work there just fine, only without the outer looping structure.
                                    1 2 Previous Next