General | ||||||||||
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 |
| |||||||||
SQL*Plus | SET SERVEROUTPUT ON in SQL*Plus is equivalent to: dbms_output.enable(buffer_size => NULL); | |||||||||
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 | ||||||||||
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; / |