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.

9 comments:

Patrick said...

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.

Denes Kubicek said...

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

Patrick said...

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

Denes Kubicek said...

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;

Anonymous said...

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?

Denes Kubicek said...

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

Denes

Unknown said...

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

Denes Kubicek said...

Infant,

You should report this in the APEX Forum at OTN.

Regards,

Denes

Siddharth Rajpoot said...

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