Plsql function exception

Plsql function exception

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 return null;
15 end;
16 /


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)
--------------------------------------------------