Examine the following PL/SQL code:
The server output is on for the session. Which statement is true about the execution of the code?
A.
It displays null if no employee with employee_id 123 exists.
B.
It produces the ora-01403: no data found error if no employee with employee_id 123 exists.
C.
It displays an error because the select into clause cannot be used to populate the PL/SQL
record type.
D.
The code executes successfully even if no employee with employee_id 123 exists and displays
Record Not Found.
B
B
b
B
When you encounter an ORA-01403 error, the following error message will appear:
ORA-01403: no data found
Cause
You tried one of the following:
You executed a SELECT INTO statement and no rows were returned.
You referenced an uninitialized row in a table.
You read past the end of file with the UTL_FILE package.
DECLARE emp_rec employee%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employee where id=1111;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Record Not found’);
ELSE
DBMS_OUTPUT.PUT_LINE(emp_rec.FIRST_NAME);
END IF;
END;
Error report –
ORA-01403: no data found
ORA-06512: at line 3
01403. 00000 – “no data found”
*Cause: No data was found from the objects.
*Action: There was no data from the objects which may be due to end of fetch.
SQL> DECLARE
2 emp_rec employees%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp_rec FROM employees where employee_id=1111;
5 IF SQL%NOTFOUND THEN
6 DBMS_OUTPUT.PUT_LINE(‘Record Not found’);
7 ELSE
8 DBMS_OUTPUT.PUT_LINE(emp_rec.FIRST_NAME);
9 END IF;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
CORRECT WAY TO WRITE THIS CODE:
============================================
DECLARE emp_rec employee%ROWTYPE;
e Exception;
BEGIN
If SQL%NOTFOUND then raise e;
else SELECT * INTO emp_rec FROM employee where id=1111;
END if;
EXCEPTION
WHEN e THEN
DBMS_OUTPUT.PUT_LINE(‘Record Not found’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(emp_rec.FIRST_NAME);
END;
/
SQL%NOTFOUND is for implicit cursor only. The exception is not handled and no data found is raised
how much of this 1z0-144 v2 exam is matching?..Im taking test tomorrow!
so how was the test?
B