Oracle PL/SQL Programming: A Developer's Workbook

Expert

4-37.	Rewrite the following PL/SQL block so that it allows each of the following SQL DML statements to execute, even if any of the others fail: BEGIN UPDATE emp SET empno = 100 WHERE empno > 5000; DELETE FROM dept WHERE deptno = 10; DELETE FROM emp WHERE deptno = 10; END;
4-38.	Write a PL/SQL block that handles by name the following Oracle error: ORA-01014: ORACLE shutdown in progress. The exception handler should display an appropriate message and then reraise the exception.
4-39.	Which of the following uses of the EXCEPTION_INIT pragma are valid, which are invalid, and why? DECLARE bad_date EXCEPTION; PRAGMA EXCEPTION_INIT (bad_date, -1843); DECLARE bad_date EXCEPTION; PRAGMA EXCEPTION_INIT (bad_date, 1843); DECLARE bad_date EXCEPTION; PRAGMA EXCEPTION_INIT (-1843, bad_date); DECLARE bad_date EXCEPTION; err_num PLS_INTEGER := -1843; PRAGMA EXCEPTION_INIT (bad_date, err_num); DECLARE bad_date EXCEPTION; PRAGMA EXCEPTION_INIT (bad_date, ORA-01843); DECLARE bad_date EXCEPTION; PRAGMA EXCEPTION_INIT (bad_date, -01843);
4-40.	What shows up on your screen when you run the following block of code in your execution environment as a top-level PL/SQL block? Explain the behavior. DECLARE d VARCHAR2(1); no_data_found EXCEPTION; BEGIN SELECT dummy INTO d FROM dual WHERE 1=2; IF d IS NULL THEN RAISE no_data_found; END IF; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE ('Trapped the error!?'); END;
4-41.	Change the code in 4-40 so that a NO_DATA_FOUND exception is trapped and handled. If you want ...