[转]Preventing ORU-10028

最新推荐文章于 2017-06-19 15:22:00 发布

cq201409

最新推荐文章于 2017-06-19 15:22:00 发布

阅读量117

点赞数

其实这个问题算老问题了，不过今天看到有人为此还钻得比较深，所以就转来收藏一下
————————————————————————————————

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:


Hi Rene,

I found that it is not convenient to have output which
is splitting words.
So I wrote small procedure to output "formatted" text,
with given width (max 255 chars), and with given
divider and end of line symbol.
Also unlike others, this procedure utilizes symbols
with more then one byte length.
Hope it will be useful.

Regards,
Oleg

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();
    print_out(, 80);
    print_out(, 20);
    print_out(, 255, ' ');
    print_out(, 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     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.