Part 2: Writing a PL/SQL program which addresses exceptions Here’s the code. Ent
ID: 3909243 • Letter: P
Question
Part 2: Writing a PL/SQL program which addresses exceptions
Here’s the code.
Enter the above PL/SQL block into the Query Builder.
Complete the remaining part as directed in the comment inside the BEGIN section of the anonymous block.
Compile and run the program by clicking on the green triangle. If you receive errors, correct them until the program runs correctly.
Part 2 Questions
Provide an example of TOO_MANY_ROWS exception by writing a small PL/SQL program. (50%)
What is a way to avoid TOO_MANY_ROWS exception? (50%)
DECLARE CURSOR CUR EMPLOYEE DETAILS AS e.employee id select e.first name e.last name e.full name, e.job id, e.salary from where employees e e.salary 50000; RV ENPLOYEE DETAILS CUR EMPLOYEE DETAILS%ROWTYPE; BEGIN OPEN CUR EMPLOYEE DETAILS FETCH CUR MPLOYEE DETAILS LOOP DBMS OUTPUT. PUT LINE ('Employee ID : rv employee_details.employee id)i ? 11 Complete the remaining display of employee' s data based on the join statement. EXIT WHEN RV EMPLOYEE DETAILS%NOTFOUND; END LOOP EXCEPTION WHEN NO DATA FOUND THEN DBMS OUTPUT. PUT LINE 'No data found') END;Explanation / Answer
Provide an example of TOO_MANY_ROWS exception by writing a small PL/SQL program. (50%)
Answer)
declare
studrec stud%rowtype;
begin
select * into studrec from stud where marks>68;
dbms_output.put_line(studrec.sn);
exception
when too_many_rows then
dbms_output.put_line('too many rows');
end;
In the above case the PL/SQL program fetches marks>68, the query results more than one row, so the too_many_rows exception fired by "exact fetch returns more than requested number of rows"
What is a way to avoid TOO_MANY_ROWS exception? (50%)
To avoid too_many_rows exception we should search our query from a primary key like
select * into studrec from stud where sid=1;
OR
we should use a cursor and by loop we display all the results.