ORA-01403: no data found

ORA-01403: no data found
Cause: No data was found from the objects.
Action: There was no data from the objects which may be due to end of fetch

Reference: Oracle Documentation

ORA-01403 is a very common error. ORA-01403 occurs with "SELECT INTO clause", which is designed to fetch only one record from a database and assign them in plsql variables. If SELECT INTO statement fails to fetch any record from database. ORA-01403 is generated.

Lets reproduce ORA-01403 with a very simple example:

SQL> create or replace function get_emp_name(p_empno emp.empno%type)
  2  return varchar2
  3  as
  4     l_ename emp.ename%type;
  5  begin
  6     select ename
  7       into l_ename
  8       from emp
  9      where empno = p_empno;
 10
 11     return l_ename;
 12  end;
 13  /

Function created.

SQL> declare
  2     l_ename emp.ename%type;
  3  begin
  4     l_ename := get_emp_name(10);
  5     dbms_output.put_line('l_ename :' || l_ename);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.GET_EMP_NAME", line 6
ORA-06512: at line 4

In above example ORA-01403 was generated because there was no record in EMP table with EMPNO=10 causing SELECT INTO statement to return with 0 record.


SOLUTION:
When you use SELECT INTO statement, your PL/SQL code should be written to handle ORA-1403 exception. PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. Following is the example which wrap up the SELECT INTO clause in proper exception handler.

SQL> create or replace function get_emp_name(p_empno emp.empno%type)
  2  return varchar2
  3  as
  4     l_ename emp.ename%type;
  5  begin
  6     select ename
  7       into l_ename
  8       from emp
  9      where empno = p_empno;
 10
 11      return l_ename;
 12  exception
 13     when NO_DATA_FOUND then
 14         -- exception handling logic goes here
 15         return null; 
 16  end;
 17  /

Function created.

SQL> set serveroutput on
SQL> declare
  2     l_ename emp.ename%type;
  3  begin
  4     l_ename := get_emp_name(10);
  5     dbms_output.put_line('l_ename :' || l_ename);
  6  end;
  7  /
l_ename :

PL/SQL procedure successfully completed.

In the above example, the GET_EMP_NAME function catches and handles the NO_DATA_FOUND exception and returns NULL if no record exists for empno passed as parameter .


One interesting thing about ORA-01403 is that if I use same "get_emp_name" function in SQL Statement even without exception handling, ORA-01403 will not be generated as in sql no data found quite simply means "no data found", stop. Let me show you this behavior by following example:

SQL> create or replace function get_emp_name(p_empno emp.empno%type)
  2  return varchar2
  3  as
  4     l_ename emp.ename%type;
  5  begin
  6     select ename
  7       into l_ename
  8       from emp
  9      where empno = p_empno;
 10
 11     return l_ename;
 12  end;
 13  /

Function created.

SQL> select get_emp_name(10) from dual;
GET_EMP_NAME(10)
--------------------------------------------------

SQL>

This is all I want to cover for ORA-01403 (NO_DATA_FOUND). I hope you have enjoyed reading this. Please put your feedback in comment box.


Related Posts:
- ORA-06502: invalid LOB locator specified
- ORA-01422: exact fetch returns more than requested number of rows
- ORA-01489: result of string concatenation is too long
- ORA-01439: column to be modified must be empty to change datatype
- ORA-01830 date format picture ends before converting entire input string
- ORA-01460 unimplemented or unreasonable conversion requested