CODEWITHSUNDEEP
oracle-apexoracle-apex-5.1
Fahad Pasha
Fahad Pasha

Reputation: 23

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

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;

Upvotes: 1

Views: 20696

Answers (4)

BillC
BillC

Reputation: 1

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

Upvotes: 0

SagarPatravale
SagarPatravale

Reputation: 1

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

Upvotes: -1

Scott
Scott

Reputation: 5035

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.

Upvotes: 2

kapiell
kapiell

Reputation: 697

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.

Upvotes: 2

Related Questions