Dbms_output of Oracle

Two minor issues in dbms_output of Oracle

 

 

1. If the content of dbms_output.put_line cannot be displayed, You need to first input set serveroutput on in the command line. Just repeat it and then the content of dbms_output.put_line will be displayed;

2. Each line of dbms_output.put_line can only display 255 characters. If it exceeds the limit, an error is returned. The error message is as follows:

ORA-20000: ORU-10028: Line Length Overflow, limit of 255 chars per line

The solution is as follows:

Declare

V_result varchar2 (1000); -- this is the string to print the result

V_pos number: = 1; -- used to record the start character position of each line of v_result

While v_pos <= length (v_result) loop

Dbms_output.put_line (substr (v_result, v_pos, 200 ));

V_pos: = v_pos + 200;

End loop;

In this way, a string of more than 255 characters can be printed, and a line break with more than 200 characters can be printed automatically.

Begin

Dbms_output.put ("put ======"); -- Do not wrap

Dbms_output.put ("put ======"); -- Do not wrap

Dbms_output.put_line ("putline ="); -- line feed

Dbms_output.put_line ("putline ="); -- line feed

Dbms_output.put_line ("putline ="); -- line feed

End;

Result:

Put ======= put ======= putline ======

Putline ======

Putline ======

 

Declare
ABC number;
Cursor cur is select 1 from dual;

Begin
Dbms_output.put_line ('cursor opened ');
Open cur;

Loop
Fetch cur into ABC;
Dbms_output.put_line ('one data ');
Exit when cur % notfound;
End loop;
Close cur;
End;

 

To use dbms_output, you must first
SQL> set serveroutput on

If too many records are output by dbms_output.put_line, the buffer overflow will occur:
ORA-20000: ORU-10027: Buffer Overflow, limit of 2000 bytes
ORA-06512: In "SYS. dbms_output", line 35
ORA-06512: In "SYS. dbms_output", line 198
ORA-06512: In "SYS. dbms_output", line 139
ORA-06512: in line 9

The solution is as follows:
(1) Use dbms_output.enable () to set the length.
Dbms_output.enable (buffer_size in integer default 20000 );
(Note: If this method fails to be set as shown above, dbms_output.enable (20000) is executed directly)

(2) You can also set serveroutput on size 5000

(1) The method is only valid for the current setting. After deletion, an error is still prompted. (2) The method modifies the configuration information. If SQL * Plus is not disabled, the setting still exists.

The output method in SQL Server is the print function:

If exists (select zip from authors where Zip = '000000 ')
Print 'berkeley author'