ORA-01403

ORA-01403: No data found

What causes this error?

An ORA-01403 error occurs when a SQL statement, written within a PL/SQL block, does not fetch any data.

How to fix it

The easiest fix would be is to handle the error in the PL/SQL block,

When a SQL statement is written within a PL/SQL block, enclose the SQL with a BEGIN and END statement. Handle the exception and raise a user-friendly message or handle the rest of the processing.

Eg:

CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS
l_empname VARCHAR2(50);
BEGIN

SELECT empname
INTO l_empname
FROM emp
WHERE empno = p_empno;

IF l_empname = 'Sarah Jones' THEN
  INSERT INTO empresult values ('105', 'Found Sarah Jones');
END IF;
END;
/

The above procedure has not handled the error that would be raised if the select statement did not find the specified empno. See the following for the error raised:

SQL> exec test_proc (1)
begin test_proc (1); end;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TAROT.TEST_PROC", line 4
ORA-06512: at line 1

If you want the procedure to execute properly without errors on the screen, then you would have to handle the error.

The Exception needs to be controlled by adding an exception handler to the code. The above code has been modified to handle an exception NO_DATA_FOUND. This is the name of the exception that the error relates to.

CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS
l_empname VARCHAR2(50);
BEGIN

SELECT empname
INTO l_empname
FROM emp
WHERE empno = p_empno;

IF l_empname = 'Sarah Jones' THEN
  INSERT INTO empresult values ('105', 'Found Sarah Jones');
END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO empresult values(p_empno, 'Did not find Sarah Jones');
END;
/

If you execute the above procedure now, this is what you would see:

SQL> exec test_proc (1)

PL/SQL procedure successfully completed.

SQL> select *
2 from empresult;

EMPNO      EMPNAME
--------- --------------------------------------------------
    1     Did not find Sarah Jones

As the exception was handled, a row was inserted into the empresult table with the error message you specified.

When the exception is raised, the control jumps from the select statement to the exception handler routine. Any code after the Select will not get executed if an exception has been raised. But if you do have some code that needs to be executed after the select has been executed, irrespective of whether the select was successful or not, then you would need to nest the begin and end statements.

CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS
l_empname VARCHAR2(50);
l_count NUMBER;
BEGIN
  BEGIN
    SELECT empname
    INTO l_empname
    FROM emp
    WHERE empno = p_empno;

    IF l_empname = 'Sarah Jones' THEN
      INSERT INTO empresult values ('105', 'Found Sarah Jones');
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO empresult values(p_empno, 'Did not find Sarah Jones');
  END;

  l_count := 1;
  -- More statements
  -- And more
END;
/

The procedure has been changed to NEST the BEGIN and END statements. So, if the SELECT statement fails, the control will enter the exception handler and then proceed on to the next line which is l_count := 1 statement.

It is always best to have separate BEGIN and END statements for every SELECT written in your PL/SQL block, which enables you to raise SELECT-senstitive error messages.

Please do not use SQL%NOTFOUND to check for this error. When the NO_DATA_FOUND exception is raised, then the program will look for the Exception handler. If it does not find one, then the error message is thrown to the screen.