ORA-01403: No data found :

In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain the most common error, which has been searched on google approximately 10 k times per month. ORA-01403 is coming when PLSQL variable does not found any data. ORERR utility may provide the information of this error, which is ‘No data found’ error. Most of the time developer forget to handle this error in PLSQL code. There might be different possible reasons for this error.

Cause and resolution of this error :

The main cause of this error is variable does not found any data. In this article I will try to give different causes for this error.

1.Select Into clause does not found any data :

This is the base reason of this error. This error will come when variable does not found any data.kindly check the following example for the same.

Example :

DECLARE

V_SRNUM VARCHAR2(20);

BEGIN

SELECT SR_NUM

INTO V_SRNUM

FROM S_SRV_REQ

WHERE ROW_ID = ‘124’;

DBMS_OUTPUT.PUT_LINE(V_SRNUM);

END;

Output :

ORA-01403: no data found

ORA-06512: at line 4

01403. 00000 –  “no data found”

*Cause:

*Action:

Resolution of this error :

Handle the exception :

To resolve this error PLSQL developer needs to handle the given exception carefully. So the code will be changed as follows :

DECLARE

V_SRNUM VARCHAR2(20);

BEGIN

SELECT SR_NUM

INTO V_SRNUM

FROM S_SRV_REQ

WHERE ROW_ID = ‘124’;

DBMS_OUTPUT.PUT_LINE(V_SRNUM);

Exception WHEN NO_DATA_FOUND THEN

dbms_output.put_line(‘Exception came’);

END;

2.Use of UTL_FILE package :

When you are using UTL_FILE package this error will occur.There was an attempt to read past the end of file when using the UTL_FILE package.

Resolution of this error :

Fix the code stop running prior to reading end of file.

3.Logical standby :

There might be the possible cause of using LOGICAL STANDBY statement. Previous use of Skip rule on DML operations causing a data mismatch. In this case simply skip the transaction and restart the apply process.