Apex 5.1 Interactive Grid Save Shows ORA-1403 No Data Found on Save When Select Statement Contains Where Clause

(Doc ID 2309654.1)

Last updated on SEPTEMBER 20, 2017

Applies to:

Oracle Application Express (formerly HTML DB) - Version 5.1.1.00.08 and later
Information in this document applies to any platform.

Symptoms

The interactive grid throws the following error when saving updates in the interactive grid when the query is filtered using a page item:

Ajax call returned server error ORA-01403: no data found for .

This error can be reproduced using the EMP table with a source query of:
SELECT emp.empno,
emp.ename,
emp.job,
emp.deptno
FROM emp WHERE :F_EXECUTE = 'EXECUTE' AND deptno = NVL (:F_DEPT, deptno);

 

Steps to reproduce:

1. Change Department field to SALES.
2. Click Search.
3. Update any row in the department column.
4. Click Save.

A different symptom can be seen with a source query of:
select
a.empno, a.ename, a.job, a.sal, a.comm, b.dname, b.loc
from emp a, dept b
where a.deptno=b.deptno
and nvl(a.comm, 0) < 1000;

In this scenario the error occurs only if you enter a value greater than 1000.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms