O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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?

  1. DECLARE
       bad_date EXCEPTION;
       PRAGMA EXCEPTION_INIT (bad_date, -1843);
  2. DECLARE
       bad_date EXCEPTION;
       PRAGMA EXCEPTION_INIT (bad_date, 1843);
  3. DECLARE
       bad_date EXCEPTION;
       PRAGMA EXCEPTION_INIT (-1843, bad_date);
  4. DECLARE
       bad_date EXCEPTION;
       err_num PLS_INTEGER := -1843;
       PRAGMA EXCEPTION_INIT (bad_date, err_num);
  5. DECLARE
       bad_date EXCEPTION;
       PRAGMA EXCEPTION_INIT (bad_date, ORA-01843);
  6. 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?

  1. Call the DBMS_UTILITY.FORMAT_ERROR_STACK to obtain the error stack, including the line numbers on which errors occurred.

  2. Examine the contents returned by SQLERRM to view the line number and program name in which the error originated.

  3. 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:

  1. Database table

  2. Database pipe

  3. Operating system file

  4. Standard output (screen)

  5. Index-by table

Characteristics:

  1. COMMIT and ROLLBACK do not affect I/O to this log.

  2. You won’t see information written to the log unless you take special action.

  3. There is a built-in limit to the volume of error data that can be written to the log.

  4. Entries written to the log become a part of the logical transaction of the application.

  5. Only someone connected to the current session can see output sent to the log, and then only when the program has stopped executing.

  6. 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.

  7. 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.)

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required