Ajax call returned server error ORA-01403: no data found for APEX Interactive Grid

1
votes

I am trying to save data into my table using an interactive grid with the help of custom plsql. I am running into an "ORA-01403-no data found" error while inserting data and I can't figure out why.

This is my plsql custom process which I run. Appreciate your help.

DECLARE

em_id   NUMBER;

BEGIN

   CASE :apex$row_status

      WHEN 'C'
      THEN
         SELECT NVL (MAX (emergency_id), 0) + 1
           INTO em_id
           FROM emp_emergency_contact;

         INSERT INTO emp_emergency_contact
                     (emergency_id, emp_id, emergency_name, emergency_relation
                     )
              VALUES (em_id, :emp_id, :emergency_name, :emergency_relation
                     );
      WHEN 'U'
      THEN
         UPDATE emp_emergency_contact
            SET emergency_name = :emergency_name,
                emergency_relation = :emergency_relation
          WHERE emergency_id = :emergency_id;
      WHEN 'D'
      THEN
         DELETE      emp_emergency_contact
               WHERE emergency_id = :emergency_id;
   END CASE;
END;
4
oracle-apexoracle-apex-5.1
I did not use the custom plsql process but I got this error when I had a where condition on the sql query of the region and when I was inserting, the process was expecting the condition to be satisfied for the column value when inserting as well. When I defaulted the value of the column so that the condition will be satisfied the issue went away. – Anuswadh

4 Answers

2
votes

So far I have not come across any documented way on how to use custom PL/SQL logic for processing submitted rows of APEX 5.1 Interactive Grid via AJAX call.

You are getting no data found error because the return is expected to be in certain json format.

The example you have provided is not too complex and can be with done using standard "Interactive Grid - Automatic Row Processing (DML)" process, which is an AJAX approach. If AJAX call is not important then you can create your own PL/SQL process with custom logic. Example of which is demonstrated in "Sample Interactive Grids" package application, check out Advanced > Custom Server Processing page in this application for more information.

I agree with Scott, you should be using a sequence or identity column for ids.

2
votes

Not entirely sure. A 'select into' can raise a no_data_found exception, but yours shouldn't.

That being said, you shouldn't have max(id)+1 anywhere in your code. This is a bug. Use a sequence or identity column instead.

0
votes

I have gotten this many times so the first thing I do is go look at any columns in my grid sql that are not part of the "Save", they are from a join for data only. I just got it again and it was a heading sort column that I had as a column type of "Number". I changed it to display only and the "Save" now works. Although, I had already set the "Source" of the column to "Query Only" which is also needed.

It is a bummer the Ajax error message doesn't at least give the column name that caused the error.

Hope this helps someone.. BillC

-1
votes

Add a RETURNING INTO clause after the insert. IG expects a primary key to be returned to query the inserted row.