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.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
Сorrect answer is B, agree with Eva
Explanation:
https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/13_elems48.htm
“If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check SQL%NOTFOUND on the next line or not.”
As no exception handlers are defined, Oracle just displays ORA-01403
Testing:
set serveroutput on;
declare
emp_rec employees%rowtype;
begin
select * into emp_rec from employees where employee_id=123;
if sql%notfound then
dbms_output.put_line(‘Record not found’);
else
dbms_output.put_line(‘Employee ‘||emp_rec.first_name||’ ‘||emp_rec.last_name||’ Salary is ‘||emp_rec.salary);
end if;
end;
/
Employee Shanta Vollman Salary is 6500
PL/SQL procedure successfully completed.
declare
emp_rec employees%rowtype;
begin
select * into emp_rec from employees where employee_id=1235;
if sql%notfound then
dbms_output.put_line(‘Record not found’);
else
dbms_output.put_line(‘Employee ‘||emp_rec.first_name||’ ‘||emp_rec.last_name||’ Salary is ‘||emp_rec.salary);
end if;
end;
/
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Agree with Eva, correct answer is B.
Explanation:
https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/13_elems48.htm
“If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check SQL%NOTFOUND on the next line or not.”
As no exception handlers are defined, Oracle just displays ORA-01403
Testing:
set serveroutput on;
declare
emp_rec employees%rowtype;
begin
select * into emp_rec from employees where employee_id=123;
if sql%notfound then
dbms_output.put_line(‘Record not found’);
else
dbms_output.put_line(‘Employee ‘||emp_rec.first_name||’ ‘||emp_rec.last_name||’ Salary is ‘||emp_rec.salary);
end if;
end;
/
Employee Shanta Vollman Salary is 6500
PL/SQL procedure successfully completed.
declare
emp_rec employees%rowtype;
begin
select * into emp_rec from employees where employee_id=1235;
if sql%notfound then
dbms_output.put_line(‘Record not found’);
else
dbms_output.put_line(‘Employee ‘||emp_rec.first_name||’ ‘||emp_rec.last_name||’ Salary is ‘||emp_rec.salary);
end if;
end;
/
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
b