Examine the following PL/SQL code:
Which statement is true about the execution of the code if the query in the PL/SQL block returns
no rows?
A.
The program abruptly terminates and an exception is raised.
B.
The program executes successfully and the output is No ROWS_FOUND.
C.
The program executes successfully and the query fetches a null value in the V_LNAME
variable.
D.
Program executes successfully, fetches a NULL value in the V_LNAME variable andan
exception is raised.
A
A
a
A
DECLARE v_lname VARCHAR2(15);
BEGIN
select last_name into v_lname from employees where job_id = 99;
IF v_lname is NULL THEN
DBMS_OUTPUT.PUT_LINE (‘No Rows found’);
ELSE
DBMS_OUTPUT.PUT_LINE (‘John ‘ || v_lname);
END IF;
END;
/
select * from employees;
OUTPUT:
ORA-01403: no data found
ORA-06512: at line 4
Cause: No data was found from the objects.
Action:There was no data from the objects which may be due to end of fetch.
TO catch the no data found error raise the Exception
===========================================================
DECLARE v_lname VARCHAR2(15);
BEGIN
select last_name into v_lname from employees where job_id = 99;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(‘No such employee exists!’);
WHEN others THEN
dbms_output.put_line(‘OTHER Error!’);
END;
/
A