Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

11-10.	Suppose that you want to erase only some of your session’s changes but retain others for saving. How can you do this?
11-11.	Write an exception section that traps any error, rolls back any changes to the last log savepoint, inserts a row into a log table and then sets a new log savepoint.
11-12.	What happens if you try to roll back to a savepoint that has not been set in your session?
11-13.	How can you make sure you never get an exception when you issue a ROLLBACK TO command?
11-14.	The following block executes a series of INSERT statements: BEGIN FOR yearnum IN 1 .. 10 LOOP SAVEPOINT start_of_loop; INSERT INTO emp (ename, deptno, empno) VALUES ('Steven' || yearnum, 10, yearnum); END LOOP; ROLLBACK TO start_of_loop; END; When this block finishes executing, how many savepoints have been set, how many savepoints are “active” (can be rolled back to), and how many rows have been inserted into the emp table (assuming that the data being inserted is valid)?
11-15.	What number is displayed on the screen for “rows deleted” when the following code is executed (assume that the employee table has 1,435 rows and that the department table has 25 rows)? CREATE OR REPLACE merge_and_purge_departments IS BEGIN DELETE FROM department; END; / BEGIN DELETE FROM employee; merge_and_purge_departments; DBMS_OUTPUT.PUT_LINE ('Rows deleted = ' || SQL%ROWCOUNT); END; /
11-16.	Suppose that you have a complex, long-running process that issues a series of queries, each of which might take half an hour ...