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 to play around with various possible solutions, check out this code in myndf.sql on this book’s web page.
4-42.	First, compile the valerr package as shown here (can you imagine a more poorly named function?): /* Filename on web page: valerr.pkg */ CREATE OR REPLACE PACKAGE valerr IS FUNCTION get RETURN VARCHAR2; END valerr; / CREATE OR REPLACE PACKAGE BODY valerr IS v VARCHAR2(1) := 'ABC'; FUNCTION get RETURN VARCHAR2 IS BEGIN RETURN v; END; BEGIN DBMS_OUTPUT.PUT_LINE ('Before I show you v...'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Trapped the error!'); END valerr; / Then call DBMS_OUTPUT.PUT_LINE to display the value returned by the valerr.get function as follows: SQL> EXEC DBMS_OUTPUT.PUT_LINE ('Value of v is ' || valerr.get); What information is displayed on the screen? What is displayed if you execute the same line of code a second time?
4-43.	You are working on a program that requires exclusive access to data in the EMP table. You attempt to lock the rows using a SELECT FOR UPDATE. If you cannot immediately acquire the lock, you should simply print a message to try again later. If you do acquire the lock, print the name of each employee using a loop.
4-44.	While helping a friend time his new program, you decide to calculate the elapsed time from executing the code as follows (note that you are better off using DBMS_UTILITY.GET_TIME instead of SYSDATE): DECLARE start_time DATE; end_time DATE; BEGIN start_time := SYSDATE; DBMS_OUTPUT.PUT_LINE ('Filler code...'); end_time := SYSDATE; DBMS_OUTPUT.PUT_LINE (end_time - start_time); END; When you execute this block, you receive the following output, but you can successfully execute the new procedure on its own: * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.STANDARD", line 1027 What could be the problem?
4-45.	Just about the most important information you can obtain about an error is the line number on which the error is raised. Which of the following statements accurately describes ways to obtain this information? Call the DBMS_UTILITY.FORMAT_ERROR_STACK to obtain the error stack, including the line numbers on which errors occurred. Examine the contents returned by SQLERRM to view the line number and program name in which the error originated. Let the error go unhandled and then view the error stack displayed in standard output (your screen, a web page, etc.).
4-46.	You have been asked to record all errors that occur in your application so they can be examined later. Identify all the different forms your error log can take inside a PL/SQL environment.
4-47.	You have decided to write your error information to a database table. You create a procedure to write to the log as follows: /* Filename on web page: log.sql */ CREATE OR REPLACE PACKAGE logpkg IS PROCEDURE putline (code_in IN INTEGER, text_in IN VARCHAR2); END; / CREATE OR REPLACE PACKAGE BODY logpkg IS CURSOR sess IS SELECT MACHINE, PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID'); rec sess%ROWTYPE; PROCEDURE putline ( code_in IN INTEGER, text_in IN VARCHAR2) IS BEGIN INSERT INTO logtab VALUES (code_in, text_in, SYSDATE, USER, SYSDATE, USER, rec.machine, rec.program); END; BEGIN OPEN sess; FETCH sess INTO rec; CLOSE sess; END; / You then test the log mechanism as follows: SQL> DECLARE 2 myval NUMBER; 3 BEGIN 4 myval := 'abc'; 5 EXCEPTION 6 WHEN OTHERS 7 THEN 8 logpkg.putline (SQLCODE, SQLERRM); 9 RAISE; 10 END; 11 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 9 But there is nothing in your log! SQL> SELECT count(*) FROM logtab; COUNT(*) --------- 0 Why didn’t the log package insert a row into the log table?
4-48.	Associate the type of error log in the first list with the characteristics in the second list: Type of error log: Database table Database pipe Operating system file Standard output (screen) Index-by table Characteristics: COMMIT and ROLLBACK do not affect I/O to this log. You won’t see information written to the log unless you take special action. There is a built-in limit to the volume of error data that can be written to the log. Entries written to the log become a part of the logical transaction of the application. Only someone connected to the current session can see output sent to the log, and then only when the program has stopped executing. The new AUTONOMOUS_TRANSACTION pragma in Oracle8i Release 8.1 allows you to write to this log and save the entry, without affecting the application’s transaction. An attempt to send error data to this log can cause your program to block or be stopped.
4-49.	Write a procedure that raises any error number passed to it, whether it be an Oracle error number (like ORA-01855), an application-specific error (in the –20,XXX range), or your own positive error numbers. (Perhaps you did not want to be constrained by the 1,000 error numbers Oracle offers. You noticed, instead, that the only positive numbers used for error handling are 1 and 100, leaving an awful lot for you.)