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

11 comments:

  1. To avoid this error, we should use cursor to fetch data in the variables. Cursor never throws " no data found" error.

    ReplyDelete
  2. Insted of fetching the data directly, one may use cursor to avoide this error, as cursor never throws"no data found" error.

    ReplyDelete
  3. Bear in mind that, as with all things, that sometimes burying a NO_DATA_FOUND handler in a function is a bad thing depending on the business requirements. In this case, on calling the function I have no way of telling if the record existed but this field value was null, or that this record does not exist. If I have logic that must know that fact, then the exception handling must be left to the calling block..

    ReplyDelete
  4. To be a complete solution, the exception handler in the function should also catch and handle TOO_MANY_ROWS which is raised by SELECT ... INTO when more than one row is found matching the WHERE-clause.

    If you are going to just return NULL, then you should probably log this exception by inserting into a log table.

    Be sure to use Autonomous Transaction to isolate the function's transaction from that of the caller, and do COMMIT before returning the value.
    - - -
    If you want to return something even when TOO_MANY_ROWS may be raised, then add AND ROWNUM = 1 to the WHERE-clause to cause just the first row to be selected.

    Of course this is not ideal as the function's return value may be somewhat random.

    Better would be to make sure that the WHERE-clause uses a primary-key or unique-key value in order to avoid TOO_MANY_ROWS exceptions and random results.

    ReplyDelete
  5. hi nimish can you explain how to explian "tell me abt urself" asa a pl/sql developer with 3+ years of experience?thanks in advance.

    ReplyDelete
  6. A common error, it can be handled with an exception, or you may trap for it with a select statement such as
    select count into v_count from table_name where where_clause
    if v_count = 1 then
    select data into v_data_value from table where where_clause;
    end if;

    ReplyDelete
  7. Don't forget that referencing to a non existent record in a plsql table type can return no-data-found too.
    And thats way more difficult error to capture then a select into, actually it's bad
    programming, but still i have seen it a lot.

    I have adopted the coding standard to skip the select into's and use cursor fetches, like my own "best
    practice".

    ReplyDelete
  8. Hello!

    Richard, you shoulnd't try to avoid ORA-01403 in such a way:

    select count into v_count from table_name where where_clause
    if v_count = 1 then
    select data into v_data_value from table where where_clause;
    end if;

    First query may return 1 into v_count, then someone commits DELETE or UPDATE in the second session, and voilĂ : here you have this NOT_DATA_FOUND exception.

    So, if you need SELECT, then just do SELECT. Do. Or do not. There is no try. ©

    You can also avoid it with FOR loop:

    for c in (select val ...
    ) loop
    l_data_value := c.val;
    end loop;

    ReplyDelete
    Replies
    1. yup, as I said it should be handled with an exception, but if you don't want to use an exception use the select. If you are worried about someone else modifying the row lock it.

      Delete
  9. Hey There. I foud your blog using msn. This is a very well written article.
    I will be sure to bookmark it and come back to read more of your useful
    information. Thanks for tthe post. I wil certainly comeback.

    ReplyDelete