Section 21.1.  Displaying Information

21.1. Displaying Information Oracle provides the DBMS_OUTPUT package to give us a way to send information from our programs to a buffer. This buffer can then be read and manipulated by another PL/SQL program or by the host environment. DBMS_OUTPUT is most frequently used as a simple mechanism for displaying information on your screen. Each user session has a DBMS_OUTPUT buffer of up to 1,000,000 bytes in size (in Oracle Database 10g and above, you can set the buffer size to UNLIMITED). You write information to this buffer by calling the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUT_LINE programs. If you are using DBMS_OUTPUT from within SQL*Plus, this information will be displayed automatically when the outermost PL/SQL block terminates. You can (optionally) explicitly retrieve information from the buffer with calls to DBMS_OUTPUT.GET and DBMS_OUTPUT.GET_LINE. 21.1.1. Enabling DBMS_OUTPUT If DBMS_OUTPUT is disabled (default setting), then calls to the PUT_LINE and PUT programs are ignored; the buffer remains empty. To enable DBMS_OUTPUT, you will generally execute a command in the host environment. For example, in SQL*Plus, you will issue this command: SET SERVEROUTPUT ON Oracle offers a variety of options for the SERVEROUTPUT command; you should check the documentation for the latest features. Here are some things you can do (you can combine the different options into a single command): Set the buffer size to the maximum allowed prior to Oracle Database 10g Release 2: SET SERVEROUTPUT ON SIZE 1000000 Set the buffer size to "unlimited" (Oracle Database 10g Release 2 only): SET SERVEROUTPUT ON In other words, the default setting in Oracle Database 10g Release 2 is an unlimited buffer size. Specify that you want the text displayed by DBMS_OUTPUT wrapped at the SQL*Plus line length. The wrapping occurs regardless of word separation. This will also stop SQL*Plus from stripping leading blanks from your text. SET SERVEROUTPUT ON FORMAT WRAPPED Specify that you want the text displayed by DBMS_OUTPUT wrapped at the SQL*Plus line length. This version respects integrity of "words." As a result, lines will be broken in a way that keeps separate tokens intact. SET SERVEROUTPUT ON FORMAT WORD_WRAPPED Specify that you want the text displayed by DBMS_OUTPUT to be truncated at the SQL*Plus line length; the rest of the text will not be displayed. SET SERVEROUTPUT ON FORMAT TRUNCATED Turn off output in SQL*Plus: SET SERVEROUTPUT OFF Third-party products such as Toad and PL/SQL Developer offer their own graphical interfaces to enabling and disabling DBMS_OUTPUT. 21.1.2. Write Lines to a Buffer Call DBMS_OUTPUT.PUT_LINE or DBMS_OUTPUT.PUT to put information into the buffer. PUT_LINE adds a newline marker after its text. PUT places text in the buffer without a newline marker. When using DBMS_OUTPUT.PUT, you should make a call to DBMS_OUTPUT.NEW_LINE to append a newline marker to the content that you previously "put." If your data can be implicitly converted to a VARCHAR2 string, then you can pass it in your call to the PUT and PUT_LINE programs. Here are some examples: BEGIN DBMS_OUTPUT.put_line ('Steven'); DBMS_OUTPUT.put_line (100); DBMS_OUTPUT.put_line (SYSDATE); END; / Unfortunately, DBMS_OUTPUT does not yet know what to do with a variety of common PL/SQL types, most notably Booleans. You may therefore want to consider writing a small utility to make it easier to display Boolean values, such as the following procedure, which displays a string and then the Boolean: /* File on web: plsb.sp */ CREATE OR REPLACE PROCEDURE plsb (str IN VARCHAR2, bool IN BOOLEAN) IS BEGIN IF bool THEN DBMS_OUTPUT.PUT_LINE (str || ' - TRUE'); ELSIF NOT bool THEN DBMS_OUTPUT.PUT_LINE (str || ' - FALSE'); ELSE DBMS_OUTPUT.PUT_LINE (str || ' - NULL'); END IF; END plsb; / In Oracle Database 10g Release 2, the maximum size string that you can pass to DBMS_OUTPUT.PUT_LINE has been raised to 32K from the previous limit of 255. If you pass a value larger than the maximum allowed, Oracle will raise an exception (either VALUE_ERROR or ORU-10028: line length overflow, limit of NNN chars per line). This is not much of a concern with a 32K limit, but prior to Oracle Database 10g Release 2, many of us encountered situations where we needed to display longer strings. If you would like to avoid this problem, you might want to use an encapsulation of DBMS_OUTPUT.PUT_LINE that automatically wraps long strings. The following files, available on the book's web site, offer variations on this theme. pl.sp This standalone procedure allows you to specify the length at which your string will be wrapped. p.pks/pkb The p package is a comprehensive encapsulation of DBMS_OUTPUT.PUT_LINE that offers many different overloadings (for example, you can display an XML document or an operating-system file by calling the p.l procedure) and also wraps long lines of text. 21.1.3. Read the Contents of a Buffer The typical usage of DBMS_OUTPUT is very basic: you call DBMS_OUTPUT.PUT_LINE and view the results on the screen. Behind the scenes, the SQL*Plus host environment calls the appropriate programs in the DBMS_OUTPUT package to extract the contents of the buffer and then display it. If you need to obtain the contents of the DBMS_OUTPUT buffer, you can call the GET_LINE and GET_LINES procedures. The GET_LINE procedure retrieves one line of information from the buffer (up to the maximum supported by DBMS_OUTPUT), and returns a status value of 0 if successful. Here's an example that uses this program to extract the next line from the buffer into a local PL/SQL variable: FUNCTION get_next_line RETURN VARCHAR2 IS return_value VARCHAR2(255); get_status INTEGER; BEGIN DBMS_OUTPUT.GET_LINE (return_value, get_status); IF get_status = 0 THEN RETURN return_value; ELSE RETURN NULL; END IF; END; The GET_LINES procedure retrieves multiple lines from the buffer with one call. It reads the buffer into a PL/SQL collection of strings (maximum length 255 or 32K, depending on your version of Oracle). You specify the number of lines you want to read, and it returns those. Here is a generic program that transfers the contents of the DBMS_OUTPUT buffer into a database log table: /* File on web: move_buffer_to_log.sp */ CREATE OR REPLACE PROCEDURE move_buffer_to_log IS l_buffer DBMS_OUTPUT.chararr; l_num_lines PLS_INTEGER; BEGIN LOOP l_num_lines := 100; DBMS_OUTPUT.get_lines (l_buffer, l_num_lines); EXIT WHEN l_buffer.COUNT = 0; FORALL indx IN l_buffer.FIRST .. l_buffer.LAST INSERT INTO logtab (text ) VALUES (l_buffer (indx) ); END LOOP; END; /