| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
|
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!
|