ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于dbms_output包的使用

关于dbms_output包的使用

原创 Linux操作系统 作者:云中雁012345 时间:2013-07-20 20:08:39 0 删除 编辑

今天开发人员忽然问起dbms_output.put_line 的使用,找了一些资料,收藏.(源自http://zhulch.itpub.net/post/17395/308697


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
Exception Name Error Code Reason
ORA-20000 ORU-10027 Buffer overflow, limit of 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);
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;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28793236/viewspace-766777/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-04-10

  • 博文量
    17
  • 访问量
    44302