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.