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