Database Reference
In-Depth Information
When running PL/SQL code, SET SERVEROUTPUT ON will
display buffered messages built within PL/SQL blocks using the
DBMS_OUTPUT.PUT procedures. These messages are useful for
debugging PL/SQL code but will only be displayed on completion
of a code block, not within the block. Additionally, the buffer has a
limited size of 2,000 characters. Increase buffer size to 1,000,000
characters using the DBMS_OUTPUT.ENABLE procedure. Thus
the following code snippet applies to large output quantities within
a PL/SQL block. The DBMS_OUTPUT.DISABLE procedure sets
the buffer back to 2,000 characters to reclaim memory space.
SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.ENABLE(1000000);
DECLARE
J INTEGER DEFAULT 1000;
BEGIN
FOR I IN 1..J LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(I)||' of '||TO_CHAR(J));
END LOOP;
END;
/
EXEC DBMS_OUTPUT.DISABLE;
SET SERVEROUTPUT OFF;
Figure 8.5 shows the result of the previous script with the buffer
(DBMS_OUTPUT.DISABLE) set to its default of 2,000 characters.
SQLP[ROMPT]
. This option changes the SQL prompt. Use the
command SET SQLPROMPT ' ' to remove the prompt altogether.
Use SET SQLPPROMPT 'SQL> ' to return to the default. The
example as shown following sets an interesting prompt, resulting in
the prompt shown in Figure 8.6. Setting ESCAPE allows output of
the period character. See ESCAPE.
COLUMN INSTANCE NEW_VALUE _INSTANCE
COLUMN USERNAME NEW_VALUE _USERNAME
SELECT INSTANCE_NAME INSTANCE FROM V$INSTANCE;
SELECT USER USERNAME FROM DUAL;
SET ESCAPE ON
SET SQLPROMPT '&_INSTANCE\.&_USERNAME> '
SET ESCAPE OFF
