This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Apr 25, 2013 11:01 AM by user626836 RSS

Unhandled Exception Forms 6i

user626836 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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