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

Q:

23-20.

The most elegant (not necessarily the most efficient) solution involves recursion:


/* Filename on web page: println.sp */
CREATE OR REPLACE PROCEDURE println (val IN VARCHAR2)
IS
BEGIN
   /* Don't display lines longer than 80 characters;
      they are hard to read. */
   IF LENGTH (val) > 80
   THEN
      DBMS_OUTPUT.PUT_LINE (SUBSTR (val, 1, 80));
      println (SUBSTR (val, 81));
   ELSE
      DBMS_OUTPUT.PUT_LINE (val);
   END IF;
END;
/

Q:

23-21.

To obtain this behavior, add the following exception section to the implementation of println:

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.ENABLE (1000000);
      println (val);
END;
/

In other words, if any problem occurs, expand the buffer to its maximum size and then try, try again.

Q:

23-22.

The most interesting aspect of the solution is the implementation of the Boolean overloading. Here is one possible approach:

 /* Filename on web page: print.pkg */ CREATE OR REPLACE PACKAGE BODY print IS PROCEDURE ln (val IN VARCHAR2) IS BEGIN IF LENGTH (val) > 80 THEN DBMS_OUTPUT.PUT_LINE (SUBSTR (val, 1, 80)); ln (SUBSTR (val, 81)); ELSE DBMS_OUTPUT.PUT_LINE (val); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.ENABLE (1000000); ln (val); END; PROCEDURE ln (val IN DATE) IS BEGIN ln (TO_CHAR (val, 'MM/DD/YYYY HH24:MI:SS')); END; PROCEDURE ln (val IN NUMBER) IS BEGIN ln (TO_CHAR (val)); ...

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