23-1.

Believe it or not, early developers inserted rows of data into a database table and then used SQL to examine what had happened. Talk about crude mechanisms!

23-2.

You can store up to 1 million bytes (not quite the same as 1 MB) in the buffer.

You can put strings, dates, and numbers into the buffer.

23-3.

Use the DBMS_OUTPUT.PUT_LINE procedure:

CREATE OR REPLACE PROCEDURE hello_world
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE ('hello world!'),
END;
/

23-4.

Call the ENABLE procedure, which also accepts as its single argument the maximum size of the buffer. In the following block, the size is set to the maximum possible:

BEGIN
   DBMS_OUTPUT.ENABLE (1000000);
END;
/

23-5.

You can call DBMS_OUTPUT.ENABLE, but that won’t be enough. You must use the following command:

SET SERVEROUTPUT ON

It calls DBMS_OUTPUT.ENABLE, but also tells SQL*Plus to automatically flush the contents of the DBMS_OUTPUT buffer to your screen when the block finishes executing.

23-6.

What you are really doing here is disabling the package, so why not call:

BEGIN
   DBMS_OUTPUT.DISABLE;
END;

23-7.

Just 2000 bytes. In other words, the following steps in SQL*Plus results in an error:

SQL> CONNECT scott/tiger
Connected.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     FOR linenum IN 1 .. 25
  3     LOOP
  4        DBMS_OUTPUT.PUT_LINE (
  5           RPAD ('rich people ', 100, 'cause poverty '));
  6     END LOOP;
  7  END;
  8  /
rich people cause poverty cause poverty cause poverty...
...
rich people cause poverty cause poverty cause poverty...
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 106
ORA-06512: at "SYS.DBMS_OUTPUT", line 65
ORA-06512: at line 4

23-8.

Create a login.sql script with the following statement in it:

SET SERVEROUTPUT ON SIZE 1000000

This file is executed automatically when you start SQL*Plus (if the login.sql file is located in the working directory).

23-9.

Here is a suggested procedure:

CREATE OR REPLACE PROCEDURE now_is_when
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (SYSDATE);
   DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE, 'HH:MI:SS'),
END;
/

23-10.

Here is one possible (and concise) implementation:

CREATE OR REPLACE PROCEDURE show_employees
IS
BEGIN
   FOR emprec IN (SELECT last_name, salary FROM employee)
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         emprec.last_name || ' earns $' || emprec.salary);
   END LOOP;
END;
/

23-11.

By default (i.e., the basic SET SERVEROUTPUT ON command), SQL*Plus trims leading blanks.

23-12.

By default, SQL*Plus pretends you didn’t ask it to display a blank line. You can override this annoying behavior with the FORMAT WRAPPED option:

SET SERVEROUTPUT ON SIZE 500000 FORMAT WRAPPED

23-13.

The WORD_WRAPPED option comes in handy for this purpose:

SET SERVEROUTPUT ON SIZE 500000 FORMAT WORD_WRAPPED

23-14.

The TRUNCATE option comes in handy for this purpose:

SET SERVEROUTPUT ON SIZE 500000 FORMAT TRUNCATE

23-15.

You get an unhandled exception:

PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

DBMS_OUTPUT.PUT_LINE is only overloaded for VARCHAR2, DATE, and NUMBER. You cannot pass it a Boolean directly.

23-16.

You get an unhandled exception:

ORA-06502: PL/SQL: numeric or value error

DBMS_OUTPUT.PUT_LINE cannot handle strings with more than 255 bytes.

23-17.

You can use either DBMS_OUTPUT.GET_LINE to extract a single line or DBMS_OUTPUT.GET_LINES to dump all of the contents into an index-by table. The following procedure dumps the buffer and returns it through the parameter list:

PROCEDURE dump_do_buffer (buffer IN OUT DBMS_OUTPUT.CHARARR)
IS
   linenum PLS_INTEGER := 1000000;
BEGIN
   DBMS_OUTPUT.GET_LINES (buffer, linenum);
END;

23-18.

Here is one possible implementation:

/* Filename on web page: putboolean.sp */
CREATE OR REPLACE PROCEDURE put_boolean (bool IN BOOLEAN)
IS
BEGIN
   IF bool
   THEN
      DBMS_OUTPUT.GET_LINES ('TRUE'),
   ELSIF bool
   THEN
      DBMS_OUTPUT.GET_LINES ('FALSE'),
   ELSE
   THEN
      DBMS_OUTPUT.GET_LINES ('NULL'),
   END IF;
END;
/

23-19.

Each time you connect to SQL*Plus, it resets the flag for SERVEROUTPUT. You have to remember to turn it back on each time you reestablish a connection to the database.

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;
/

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.

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));
   END;

   PROCEDURE ln (val IN BOOLEAN) IS
   BEGIN
      IF val
      THEN
         ln ('TRUE'),
      ELSIF NOT val
      THEN
         ln ('FALSE'),
      ELSE
         ln (''),
      END IF;
   END;
END print;
/

23-23.

The various PUT procedures all modify the contents of the DBMS_OUTPUT buffer, which is a package variable data structure. Therefore, Oracle could not assert WNPS or RNPS on these (and any other) DBMS_OUTPUT procedures.

23-24.

Remember that the DBMS_OUTPUT buffer is only flushed to the screen by SQL*Plus when the PL/SQL block terminates. The “SELECT … FROM emp” query executed is not a PL/SQL block, so when it finishes running, the DBMS_OUTPUT buffer is not flushed. To see the trace information, it is necessary to run some PL/SQL block after the SQL statement. This block does not need to call DBMS_OUTPUT.PUT_LINE; it only needs to complete successfully. Even if you execute a do-nothing block, as shown next, you will then see the trace information (and the new Oracle8 extended ROWID format):

SQL> BEGIN NULL; END;
  2  /
emp-AAAAfBAACAAAAEqAAA
...
emp-AAAAfBAACAAAAEqAAN

Of course, if you run this query from within a PL/SQL program, its output is displayed when the program finishes.

23-25.

Row 1 is the starting point for GET_LINES.

23-26.

A tricky one! Take it one step at a time:

Here is one possible implementation:

/* Filename on web page: xbuff.pkg */
CREATE OR REPLACE PACKAGE xbuff
IS
   contents DBMS_OUTPUT.CHARARR;
   PROCEDURE dumpit;
END;
/
CREATE OR REPLACE PACKAGE BODY xbuff
IS
   PROCEDURE dumpit
   IS
      tempbuff DBMS_OUTPUT.CHARARR;
      linenum PLS_INTEGER := 1000;
   BEGIN
      DBMS_OUTPUT.GET_LINES (tempbuff, linenum);
      IF linenum > 0
      THEN
         linenum := tempbuff.FIRST;
         LOOP
            EXIT WHEN linenum IS NULL;
            contents (NVL (contents.LAST, 0) + 1) := tempbuff(linenum);
            linenum := tempbuff.NEXT (linenum);
         END LOOP;
      END IF;
   END;
END;
/

The following test script shows the storage of approximately 5 MB of data:

/* Filename on web page: xbuff.tst */
CONNECT scott/tiger
SET SERVEROUTPUT ON
DECLARE
   PROCEDURE fill_and_dump
   IS
   BEGIN
      /* 1000 bytes per round. */
      FOR indx IN 1 .. 50
      LOOP
         DBMS_OUTPUT.PUT_LINE ('PL/SQL and Java both'),
      END LOOP;
      xbuff.dumpit;
      DBMS_OUTPUT.DISABLE;
      DBMS_OUTPUT.ENABLE (2000);
   END;
BEGIN
   /* Buffer is set to 2000. Let's fill it up,
      then dump it to my buffer, disable, re-enable,
      fill it again, dump it and see what the total
      amount is in xbuff.contents. */
   FOR oneK IN 1 .. 100
   LOOP
      fill_and_dump;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE (
      xbuff.contents.COUNT * 1000 ||
      ' bytes through DBMS_OUTPUT.PUT_LINE in one block.'),
END;
/

You’ll see the following results:

SQL> @xbuff.tst
Connected.
5100000 bytes through DBMS_OUTPUT.PUT_LINE in one block.

23-27.

Curious, isn’t it? The error occurs because another session (A) has changed the definition of (recompiled) code that was already referenced in “your” session (B). Information about any code is cached in the session’s private memory area; it is now, however, out of date, and that causes the error.

DBMS_OUTPUT.PUT_LINE displays nothing because the package has been set to “disabled.” That may seem ridiculous on the face of it, since you issued a call to SET SERVEROUTPUT ON to enable the package. That was true, but take a look at the error stack information:

ORA-04068: existing state of packages has been discarded

What this is saying is that the “states” (values of data structures) of all the packages used by the session have been set back to their defaults. This includes DBMS_OUTPUT, and the default state is to be disabled.

So whenever you get this error in SQL*Plus, you should either reconnect, or run your login.sql script to reinitialize your settings.