René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Preventing ORU-10028 | ||
The following seemingly harmless statement produces an ORU-10028: line length overflow, limit of 255 bytes per line error:
begin dbms_output.put_line(rpad('x',440)); end;
This is because dbms_output.put_line cannot print lines that exceed 255 characters.
In order to prevent these errors, the following substitute-function can be used. It automatically wraps lines
that are too long into shorter pieces.
create or replace procedure put_line( v_string in varchar2, v_len in integer) as v_curr_pos integer; v_length integer; v_printed_to integer; v_last_ws integer; skipping_ws boolean; begin if (v_string is null) then return; end if; v_length := length(v_string); v_curr_pos := 0; v_printed_to := -1; v_last_ws := -1; skipping_ws := true; while v_curr_pos < v_length loop if substr(v_string,v_curr_pos+1,1) = ' ' then v_last_ws := v_curr_pos; if skipping_ws then v_printed_to := v_curr_pos; end if; else skipping_ws := false; end if; if v_curr_pos >= v_printed_to + v_len then if v_last_ws <= v_printed_to then dbms_output.put_line(substr(v_string,v_printed_to+2,v_curr_pos-v_printed_to)); v_printed_to:=v_curr_pos; skipping_ws := true; else dbms_output.put_line(substr(v_string,v_printed_to+2,v_last_ws-v_printed_to)); v_printed_to := v_last_ws; skipping_ws := true; end if; end if; v_curr_pos := v_curr_pos + 1; end loop; dbms_output.put_line (substr(v_string,v_printed_to+1)); end put_line; /
The first parameter is the string to be printed and the second is the maximum line width.
An example for how to use this function can be found in a script that accesses v$sql_text_with_newlines.
A more sophisticated solution
Reinhard Ueberschär pointed out an error in the procedure above and sent me a better
and more sophsticated solution. I am happy to puplish it here:
------------------------------------------------------------------------- -- Output procedure that inserts line breaks into dbms_output ------------------------------------------------------------------------- PROCEDURE put_line (p_string IN VARCHAR2,p_compress IN BOOLEAN) IS v_curr_pos INTEGER; v_length INTEGER; v_printed_to INTEGER; v_last_ws INTEGER; skipping_ws BOOLEAN; c_len CONSTANT INTEGER := 160; ------------------------------------------------------ -- All 3 variables must be modified at the same time. c_max_len CONSTANT INTEGER := 10000; v_string VARCHAR2 (10002); ------------------------------------------------------ nl CONSTANT VARCHAR2 (3) := CHR (10); cr CONSTANT VARCHAR2 (3) := CHR (13); v_len_total INTEGER; BEGIN ------------------------------------------------------------------------- -- Case 1: Null string. ------------------------------------------------------------------------- IF (p_string IS NULL) THEN DBMS_OUTPUT.new_line; RETURN; END IF; ------------------------------------------------------------------------- -- Case 2: Recursive calls for very long strings! (hard line breaks) ------------------------------------------------------------------------- v_len_total:=LENGTH (p_string); IF (v_len_total > c_max_len) THEN put_line(SUBSTR (p_string, 1, c_max_len),p_compress); put_line(SUBSTR (p_string, c_max_len+1, v_len_total-c_max_len),p_compress); RETURN; END IF; ------------------------------------------------------------------------- -- Case 3: Regular start here. ------------------------------------------------------------------------- v_string := p_string; ------------------------------------------------------------------------- -- Remove EOL characters! ------------------------------------------------------------------------- IF (p_compress) --compressed mode THEN -- -- Strip all linefeed characters -- v_string := REPLACE (v_string, CHR (10), ' '); --New Line v_string := REPLACE (v_string, CHR (13), ' '); --Carriage Return ELSE -- -- Strip only last linefeed characters -- v_string := RTRIM (v_string, CHR (10)); --New Line v_string := RTRIM (v_string, CHR (13)); --Carriage Return END IF; -------------------------------------------------------------------------- -- Main algorithm -------------------------------------------------------------------------- v_length := LENGTH (v_string); v_curr_pos := 1; -- current position (Start with 1.ch.) v_printed_to := 0; -- string was printed to this mark v_last_ws := 0; -- position of last blank skipping_ws := TRUE; -- remember if blanks may be skipped WHILE v_curr_pos <= v_length LOOP IF SUBSTR (v_string, v_curr_pos, 1) = ' ' -- blank found THEN v_last_ws := v_curr_pos; ---------------------------------------- -- if in compress mode, skip any blanks ---------------------------------------- IF (p_compress AND skipping_ws) THEN v_printed_to := v_curr_pos; END IF; ELSE skipping_ws := FALSE; END IF; IF (v_curr_pos >= (v_printed_to + c_len)) THEN IF ( (v_last_ws <= v_printed_to) -- 1) no blank found OR -- 2) next char is blank -- (ignore last blank) ((v_curr_pos < v_length) AND (SUBSTR(v_string,v_curr_pos+1,1) = ' ')) OR (v_curr_pos = v_length) -- 3) end of string ) THEN ------------------------------------- -- Hard break (no blank found) ------------------------------------- DBMS_OUTPUT.put_line (SUBSTR (v_string, v_printed_to + 1, v_curr_pos - v_printed_to ) ); v_printed_to := v_curr_pos; skipping_ws := TRUE; ELSE ---------------------------------- -- Line Break on last blank ---------------------------------- DBMS_OUTPUT.put_line (SUBSTR (v_string, v_printed_to + 1, v_last_ws - v_printed_to ) ); v_printed_to := v_last_ws; IF (v_last_ws = v_curr_pos) THEN skipping_ws := TRUE; END IF; END IF; END IF; v_curr_pos := v_curr_pos + 1; END LOOP; DBMS_OUTPUT.put_line (SUBSTR (v_string, v_printed_to + 1)); END put_line; ------------------------------------------ -- Default: no compression ------------------------------------------ PROCEDURE put_line (p_string IN VARCHAR2) IS BEGIN put_line(p_string,FALSE); END put_line; Oleg Savkin's improvement
Oleg Savkin has sent me another improved version. He writes:
Of course, I find it useful, and I am happy to put it on this page:
/* *************************************** Procedure to print big text on screen Parameters: IN_TEXT - text to print IN_TEXT_LENGTH - output string length. Default is 255 (maximum allowed for DBMS_OUTPUT) IN_DIVIDER - divider between words. Used to do not split the whole word when start new print line Default is SPACE IN_NEW_LINE - new line divider. If there is this divider withing string to print out, then string will be first printed till this divider, and then start from new line. Default NULL Examples: print_out(<text>); print_out(<text>, 80); print_out(<text>, 20); print_out(<text>, 255, ' '); print_out(<text>, 250, ' ', chr(10)); Last example: print text breaking it by spaces. If there is new line character within test, it will be printed on the different line. Author: Oleg Savkin Nov 2005 * ***************************************/ CREATE OR REPLACE PROCEDURE print_out( IN_TEXT VARCHAR2, IN_TEXT_LENGTH NUMBER DEFAULT 255, IN_DIVIDER VARCHAR2 DEFAULT CHR(32), IN_NEW_LINE VARCHAR2 DEFAULT NULL) IS lv_print_text VARCHAR2(32767); ln_position PLS_INTEGER; ln_divider_position PLS_INTEGER; ln_total_printed PLS_INTEGER; ln_string_length PLS_INTEGER; PROCEDURE printText (IN_PRINT VARCHAR2) IS BEGIN dbms_output.put_line( IN_PRINT ); END printText; BEGIN IF IN_TEXT_LENGTH >255 THEN ln_string_length := 255; ELSE ln_string_length := IN_TEXT_LENGTH; END IF; IF LENGTHB(IN_TEXT) <=IN_TEXT_LENGTH THEN printText(IN_TEXT); ELSE ln_position := 1; ln_total_printed := 0; LOOP lv_print_text := SUBSTR( IN_TEXT,ln_position, ln_string_length ); IF IN_NEW_LINE IS NULL THEN ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider ELSE ln_divider_position := INSTR(lv_print_text, IN_NEW_LINE, -1); IF ln_divider_position = 0 THEN ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider END IF; END IF; IF ln_divider_position = 0 THEN ln_divider_position := ln_string_length; END IF; IF ln_divider_position <=ln_string_length THEN lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position); IF length( lv_print_text ) <> lengthb(lv_print_text) THEN ln_divider_position := ln_divider_position-(lengthb(lv_print_text)-length( lv_print_text )); lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position); IF IN_NEW_LINE IS NULL THEN ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider ELSE ln_divider_position := INSTR(lv_print_text, IN_NEW_LINE, -1); IF ln_divider_position = 0 THEN ln_divider_position := INSTR(lv_print_text, IN_DIVIDER, -1); -- get position for the last divider END IF; END IF; IF ln_divider_position = 0 THEN ln_divider_position := ln_string_length-(lengthb(lv_print_text)-length( lv_print_text )); END IF; lv_print_text := SUBSTR( IN_TEXT, ln_position, ln_divider_position); END IF; IF ln_divider_position = 0 THEN ln_divider_position := ln_string_length; END IF; ln_position := ln_position+ln_divider_position; END IF; ln_total_printed := ln_total_printed+LENGTHB(lv_print_text); lv_print_text := TRIM( lv_print_text ); --dbms_output.put_line('***'); printText(lv_print_text); EXIT WHEN ln_position >= LENGTH(TRIM(IN_TEXT)); END LOOP; IF ln_position <ln_total_printed -- printed not everything THEN printText(substr( IN_TEXT, ln_position, ln_total_printed )); END IF; END IF; EXCEPTION WHEN others THEN dbms_output.put_line( 'ERROR :'||SQLERRM ); dbms_output.put_line( 'ln_position: '||ln_position ); dbms_output.put_line( 'ln_divider_position: '||ln_divider_position ); END print_out; / show error
Testing print_out:
set serveroutput on size 1000000 format wrapped begin print_out( 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam ' || 'nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. ' || 'Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit ' || 'lobortis nisl ut aliquip ex ea commodo consequat. Duis autem vel eum iriure ' || 'dolor in hendrerit in vulputate velit esse molestie consequat, vel illum ' || 'dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio ' || 'dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te ' || 'feugait nulla facilisi. Nam liber tempor cum soluta nobis eleifend option ' || 'congue nihil imperdiet doming id quod mazim placerat facer possim assum.', 100); end; / Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Update
January 8th 2005: Chris Purdom found two typos and lets me know about them. Thanks very much! They're fixed now.
July 20th 2005: John Hawksworth finds a typo and and lets me know about it. Also thank you very much!
|