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 and an exception is raised.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
I agree that correct answer is A, but error is different.
In question it’s said: “if the query in the PL/SQL block returns no rows?”
So, if we modify fist_name to ‘John1′ we can get this behavior:
DECLARE
v_lname VARCHAR2(15);
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE first_name=’John1’;
IF v_lname is NULL THEN
DBMS_OUTPUT.PUT_LINE(‘No Rows found’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘John”s last name is: ‘||v_lname);
END IF;
END;
/
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
DECLARE
v_lname VARCHAR2(15);
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE first_name=’John’;
IF v_lname is NULL THEN
DBMS_OUTPUT.PUT_LINE(‘No Rows found’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘John”s last name is: ‘||v_lname);
END IF;
END;
/
ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4
ORA-06512: at “SYS.DBMS_SQL”, line 1721
THERE ARE MORE THAN 1 EMPLOYEES WITH NAME ‘JOHN’.
‘A’ CORRECT ANSWER