Handle ORA-01403: no data found in PLSQL

Handle ORA-01403: no data found

Error
ORA-01403: no data found


SET SERVEROUTPUT ON
DECLARE
v_department_id NUMBER := &sv_department_id;
v_department_name varchar2(50);
BEGIN
SELECT department_name INTO v_department_name FROM departments WHERE department_id = v_department_id;

DBMS_OUTPUT.PUT_LINE ('Department Name is: '||v_department_name);
END;
/

Enter value for sv_department_id: 2
old 2: v_department_id NUMBER := &sv_department_id;
new 2: v_department_id NUMBER := 2;
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

Cause:
Table does not have data according to where condition.

Solution:
In PL/SQL you can use the exceptional handling with NO DATA FOUND or SQL CODE
When error occured SQLCODE is 100 value as show shown in second example.

SET SERVEROUTPUT ON
DECLARE
v_department_id NUMBER := &sv_department_id;
v_department_name varchar2(50);
BEGIN
SELECT department_name INTO v_department_name FROM departments WHERE department_id = v_department_id;

DBMS_OUTPUT.PUT_LINE ('Department Name is: '||v_department_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No Department is Present');

END;
/

SQL> /
Enter value for sv_department_id: 1
old 2: v_department_id NUMBER := &sv_department_id;
new 2: v_department_id NUMBER := 1;
No Department is Present

PL/SQL procedure successfully completed.

SQLCODE value is 100 when NO DATA FOUND

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_department_id NUMBER := &sv_department_id;
3 v_department_name varchar2(50);
4 BEGIN
5 SELECT department_name INTO v_department_name FROM departments WHERE department_id = v_department_id;
6
7 DBMS_OUTPUT.PUT_LINE ('Department Name is: '||v_department_name);
8 DBMS_OUTPUT.PUT_LINE(sqlcode);
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE ('No Department is Present');
12 DBMS_OUTPUT.PUT_LINE(sqlcode);
13
14 END;
15 /
Enter value for sv_department_id: 1
old 2: v_department_id NUMBER := &sv_department_id;
new 2: v_department_id NUMBER := 1;
No Department is Present
100

PL/SQL procedure successfully completed.

SQLCODE is 0 when successfully done

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_department_id NUMBER := &sv_department_id;
3 v_department_name varchar2(50);
4 BEGIN
5 SELECT department_name INTO v_department_name FROM departments WHERE department_id = v_department_id;
6
7 DBMS_OUTPUT.PUT_LINE ('Department Name is: '||v_department_name);
8 DBMS_OUTPUT.PUT_LINE(sqlcode);
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE ('No Department is Present');
12 DBMS_OUTPUT.PUT_LINE(sqlcode);
13
14 END;
15 /
Enter value for sv_department_id: 10
old 2: v_department_id NUMBER := &sv_department_id;
new 2: v_department_id NUMBER := 10;
Department Name is: Administration
0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.