| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsotpt.sql |
| First Available |
7.3.4 |
| Data Types |
TYPE chararr IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
-- Note: was 255 bytes in 10gR1 and earlier
TYPE dbmsoutput_linesarray IS
VARRAY(2147483647) OF VARCHAR2(32767); |
| Dependencies |
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_OUTPUT'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_OUTPUT'; |
| Exceptions |
| Exception Name |
Error Code |
Reason |
| ORA-20000 |
ORU-10027 |
Buffer overflow, limit of <buf_limit> bytes |
| ORA-20000 |
ORU-10028 |
Line length overflow, limit is 32767 bytes per line |
|
| SQL*Plus |
SET SERVEROUTPUT ON in SQL*Plus is equivalent to:
dbms_output.enable(buffer_size => NULL); |
| Security Model |
Execute is granted to PUBLIC. Package is explicitly AUTHID DEFINER as of 11.2. |
| Subprograms |
|
| |
| DISABLE |
| Disable DBMS_OUTPUT and reset the buffer size to the default |
dbms_output.disable; |
| exec dbms_output.disable; |
| |
| ENABLE |
| Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000 |
dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000); |
| exec dbms_output.enable(1000000); |
| |
| GET_LINE |
| Returns a single line of buffered information |
dbms_output.get_line(line OUT VARCHAR2, status OUT INTEGER); |
set serveroutput on
DECLARE
buffer VARCHAR2(100);
status INTEGER;
BEGIN
dbms_output.put_line('This is');
dbms_output.put_line('a test.');
dbms_output.get_line(buffer, status);
dbms_output.put_line('Buffer: ' || buffer);
dbms_output.put_line('Status: ' || TO_CHAR(status));
END;
/ |
| |
| GET_LINES |
Retrieves an array of lines from the buffer
Overload 1 |
dbms_output.get_lines(lines OUT CHARARR, numlines IN OUT INTEGER); |
set serveroutput on
DECLARE
outtab dbms_output.chararr;
fetchln INTEGER := 15;
BEGIN
outtab(1) := 'This is a test';
outtab(12) := 'of dbms_output.get_lines';
dbms_output.put_line('A: ' || outtab(1));
dbms_output.put_line('A: ' || outtab(12));
dbms_output.get_lines(outtab, fetchln);
dbms_output.put_line(TO_CHAR(fetchln));
/*
FOR i IN 1 .. fetchln LOOP
dbms_output.put_line('B: ' || outtab(i));
END LOOP;
*/
END;
/
DECLARE
outtab dbms_output.chararr;
fetchln INTEGER := 15;
BEGIN
outtab(1) := 'This is a test';
outtab(12) := 'of dbms_output.get_lines';
dbms_output.put_line('A: ' || outtab(1));
dbms_output.put_line('A: ' || outtab(12));
dbms_output.get_lines(outtab, fetchln);
dbms_output.put_line(TO_CHAR(fetchln));
FOR i IN 1 .. fetchln LOOP
dbms_output.put_line('B: ' || outtab(i));
END LOOP;
END;
/ |
| Overload 2 |
dbms_output.get_lines(
lines OUT dbmsoutput_linesarray,
numlines IN OUT INTEGER); |
set serveroutput on
BEGIN
dbms_output.put_line(lo(1));
END;
/
===========================================
DECLARE
lo dbmsoutput_linesarray := dbmsoutput_linesarray(10);
fetchln INTEGER := 15;
BEGIN
lo(1) := 'ABC';
lo.extend;
lo(2) := 'DEF';
lo.extend;
lo(3) := 'GHI';
lo.extend;
lo(4) := 'JKL';
lo.extend;
lo(5) := 'MNO';
dbms_output.put_line('A: ' || lo(1));
dbms_output.put_line('A: ' || lo(2));
dbms_output.put_line('A: ' || lo(3));
dbms_output.put_line('A: ' || lo(4));
dbms_output.put_line('A: ' || lo(5));
dbms_output.get_lines(lo, fetchln);
dbms_output.put_line(TO_CHAR(fetchln));
/*
FOR i IN 1 .. fetchln LOOP
dbms_output.put_line('B: ' || outtab(i));
END LOOP;
*/
END;
/
DECLARE
lo dbmsoutput_linesarray := dbmsoutput_linesarray(10);
fetchln INTEGER := 15;
BEGIN
lo(1) := 'ABC';
lo.extend;
lo(2) := 'DEF';
lo.extend;
lo(3) := 'GHI';
lo.extend;
lo(4) := 'JKL';
lo.extend;
lo(5) := 'MNO';
dbms_output.put_line('A: ' || lo(1));
dbms_output.put_line('A: ' || lo(2));
dbms_output.put_line('A: ' || lo(3));
dbms_output.put_line('A: ' || lo(4));
dbms_output.put_line('A: ' || lo(5));
dbms_output.get_lines(lo, fetchln);
dbms_output.put_line(TO_CHAR(fetchln));
FOR i IN 1 .. fetchln LOOP
dbms_output.put_line('B: ' || lo(i));
END LOOP;
END;
/ |
| |
| NEW_LINE |
| Inserts an end-of-line marker |
dbms_output.new_line; |
set serveroutput on
BEGIN
dbms_output.enable(9999999);
dbms_output.new_line();
FOR rec IN (SELECT table_name FROM user_tables)
LOOP
dbms_output.put_line (rec.table_name);
END LOOP;
dbms_output.new_line();
END;
/ |
| |
| PUT |
| Put a piece of information in the buffer |
dbms_output.put(a IN VARCHAR2); |
| Obsolete and no longer supported by Oracle |
| |
| PUT_LINE |
| Output a literal |
dbms_output.put_line(a IN VARCHAR2); |
set serveroutput on
BEGIN
dbms_output.put_line('Display a string literal');
END;
/ |
| Output a variable |
set serveroutput on size 1000000 format wrapped
DECLARE
x VARCHAR2(20) := RPAD('Dan Morgan', 199, 'x')
BEGIN
dbms_output.put_line(x);
END;
/ |
