Dbms_output of oracle

Oracle dbms_output two minor issues of dbms_output in 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, after dbms_output.put_line content can be displayed; www.2cto.com 2, dbms_output.put_line each line can only display 255 characters, exceeding the will report an error, the error content is as follows ORA-20000: ORU-10028: line overlength flow, limit of 255 chars per line to solve this problem: www.2cto.com declare v_result Varchar2 (1000); -- this is the string v_pos Number: = 1 to print the result; -- used to record the location of start characters in each row of v_result WH ILE 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, you can print a string of more than 255 characters, and automatically wrap the string of more than 200 characters to print out begin DBMS_OUTPUT.put ("put ====== "); -- Do not wrap DBMS_OUTPUT.put ("put ="); -- Do not wrap DBMS_OUTPUT.put_line ("putline = "); -- newline DBMS_OUTPUT.put_line ("putline ="); -- newline DBMS_OUTPUT.put_line ("putline ="); -- newline end; Result: put ======= put ===== put Line ======= 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 dbms_output.put_line output too many records, there will be buffer overflow: ORA-20000: ORU-10027: buffer overflow, limit of 2000 byt EsORA-06512: In "SYS. DBMS_OUTPUT ", line 35ORA-06512: In" SYS. DBMS_OUTPUT ", line 198ORA-06512: In" SYS. DBMS_OUTPUT ", line 139ORA-06512: Solution on line 9 is as follows: www.2cto.com (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 above, direct DBMS_OUTPUT.ENABLE (20000); then it is successfully executed.) (2) you can also set serveroutput on size 5000. The (1) method is only valid for the current setting. An error will still be prompted after deletion, and the (2) method is used to modify the configuration information, this setting still exists if SQL * plus is not disabled. The output method in SQL Server is the PRINT function: IF EXISTS (SELECT zip FROM authors WHERE zip = '000000') PRINT 'berkeley author'