Thursday, 28 June 2012

APEX Tabular Form and ORA-01403: no data found

If you get the "ORA-01403: no data found" error after running your code while updating or validating a tabular form, you will need to find out which array is causing it. This error occurs if the referenced array does not exist - apex_application.g_f01..g_f50. For example, if you use a PL/SQL block similar to this: 
DECLARE
   vrow   BINARY_INTEGER;
BEGIN
   FOR i IN 1 .. apex_application.g_f01.COUNT
   LOOP
      vrow := apex_application.g_f01 (i);

      UPDATE dept
         SET dname = apex_application.g_f04 (vrow),
             loc = apex_application.g_f05 (vrow)
       WHERE empno = apex_application.g_f02 (vrow);
   END LOOP;
END;
The easiest way to debug is to use:

1. Firefox
2. Firebug

Activate the firebug and use the HTML option to go over the elements (columns) in your tabular form. Firebug will show the associated array number and you can use that information to correct you code.

on

9 comments:

  1. Patrick25 September 2012 at 18:06

    Hi

    Is there a way to validate if an array exist ? For instance, if you have an apex collection with different cascading LOV, some of them are empty until the user hits the previous LOV. You want to validate that all the fields have been entered on Submit.

    ReplyDelete
  2. Denes Kubicek25 September 2012 at 19:41

    You can put a block inside your code with exception handling.

    ReplyDelete
  3. Patrick25 September 2012 at 22:00

    Thanks for your quick reply...Do you have a quick example of the code and where do you put it. For instance, i want to validate if (apex_application.g_f04 (i)) exist, if not I want to force the user to enter the value.

    Thanks again

    ReplyDelete
  4. Denes Kubicek25 September 2012 at 22:15

    BEGIN
    FOR i IN 1 .. apex_application.g_f02.COUNT
    LOOP
    DECLARE
    v_value VARCHAR2 (4000);
    v_error VARCHAR2 (4000);
    BEGIN
    v_value := apex_application.g_f04 (i);
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    v_error := 'Array doesn''t exist.';
    RETURN v_error;
    END;
    END LOOP;
    END;

    ReplyDelete
  5. Anonymous28 November 2013 at 19:57

    Hi,

    I am trying to create a report based on an ancillary operator, i.e.;

    SDO_NN_DISTANCE(1) "DIST" -> retrieves ordered list (nearest first) of results based on location (I'm using an MDSYS.SDO_GEOMETRY object).
    I have created a query which works fine and does the job in SQLDeveloper; however, when I implement it in APEX as a PL/SQL body function returning an SQL query, I only append to the string the DIST column should the user wish to order by location. This causes a ORA-01403: no data found; the debug log shows a correct query (working in SQLDeveloper), but the error is produced in APEX.

    Any idea how I could overcome this issue?

    ReplyDelete
  6. Denes Kubicek29 November 2013 at 08:05

    How does that query look like? It should create a tabular form based on a PL/SQL Function returning a query?

    Denes

    ReplyDelete
  7. Unknown22 May 2015 at 12:14

    Hi Denes,

    I'm facing the similar issue in my tabular form... Before creating validations for some columns its working fine... After creating validations, if validation failure in my tabular form it returns

    report error:
    ORA-01403: no data found
    ORA-06510: PL/SQL: unhandled user-defined exception

    I'm using apex 5.0
    Need your help on this...

    Thanks
    Infantraj

    ReplyDelete
  8. Denes Kubicek22 May 2015 at 14:14

    Infant,

    You should report this in the APEX Forum at OTN.

    Regards,

    Denes

    ReplyDelete
  9. Siddharth Rajpoot1 May 2016 at 15:09

    Me too getting the same error whenever even a simple validation fails. Trying to find the work-around for now.

    ReplyDelete