Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38

    Unanswered: line length overflow

    How can I get around this? It's caused by a 'put_line' in a PL/SQL package.

    -20000: ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
    Cordialement

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: line length overflow

    Originally posted by Crassus
    How can I get around this? It's caused by a 'put_line' in a PL/SQL package.

    -20000: ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
    DBMS_OUTPUT.PUT_LINE cannot handle more than 255 bytes per line of output. You will have to break the lines up into chunks of 255 characters. One way is to write your own "wrapper" procedure that you call instead of DBMS_OUTPUT.PUT_LINE.

  3. #3
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38
    PROCEDURE put_lines (sText IN VARCHAR2)
    AS
    nCharsInText PLS_INTEGER;
    nPos PLS_INTEGER;
    c_nOutputLimit CONSTANT PLS_INTEGER := 255;
    sBloc VARCHAR2 (255);
    BEGIN
    nPos := 0;
    sBloc := '';

    SELECT LENGTH (sText)
    INTO nCharsInText
    FROM DUAL;

    WHILE (nPos <= nCharsInText)
    LOOP
    SELECT SUBSTR (sText, nPos, c_nOutputLimit)
    INTO sBloc
    FROM DUAL;

    DBMS_OUTPUT.PUT_LINE (sBloc);
    nPos := nPos + c_nOutputLimit + 1;
    END LOOP;
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    END put_lines;
    Cordialement

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Slight correction needed, otherwise skips the odd character:

    PROCEDURE put_lines (sText IN VARCHAR2)
    AS
    nCharsInText PLS_INTEGER;
    nPos PLS_INTEGER;
    c_nOutputLimit CONSTANT PLS_INTEGER := 255;
    sBloc VARCHAR2 (255);
    BEGIN
    nPos := 1;
    sBloc := '';

    SELECT LENGTH (sText)
    INTO nCharsInText
    FROM DUAL;

    WHILE (nPos <= nCharsInText)
    LOOP
    SELECT SUBSTR (sText, nPos, c_nOutputLimit)
    INTO sBloc
    FROM DUAL;

    DBMS_OUTPUT.PUT_LINE (sBloc);
    nPos := nPos + c_nOutputLimit;
    END LOOP;
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.PUT_LINE (SQLERRM);
    END put_lines;

  5. #5
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38
    Is there a way that I can generate a string of n characters in PL/SQL (easier for unit testing)?

    Thanks for the correction. Much appreciated.
    Cordialement

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Crassus
    Is there a way that I can generate a string of n characters in PL/SQL (easier for unit testing)?

    Thanks for the correction. Much appreciated.
    Yes: RPAD('*',1000,'*') will return a string of 1000 *'s

    However, that wouldn't help you spot a problem like I found above!

    Actually what I did was (having suspected a problem) change the limit from 255 to 5 and then test with a shorter string like 'abcdefghijklmn'

  7. #7
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38
    Not bad.
    Cordialement

  8. #8
    Join Date
    May 2003
    Posts
    87
    And instead of those frequent calls to dual table, you could directly assign values.

    Code:
    SELECT LENGTH (sText)
    INTO nCharsInText
    FROM DUAL;
    
    OR
    
    nCharsInText := LENGTH(sText);
    Code:
    SELECT SUBSTR (sText, nPos, c_nOutputLimit)
    INTO sBloc
    FROM DUAL;
    
    OR
    
    sBloc := SUBSTR(sText, nPos, c_nOutputLimit);

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Slimmed down version:
    Code:
    create or replace
    PROCEDURE put_lines (sText IN VARCHAR2)
    AS
      nCharsInText PLS_INTEGER := LENGTH (sText);
      nPos PLS_INTEGER := 1;
      c_nOutputLimit CONSTANT PLS_INTEGER := 5;
    BEGIN
      WHILE (nPos <= nCharsInText)
      LOOP
        DBMS_OUTPUT.PUT_LINE (SUBSTR (sText, nPos, c_nOutputLimit));
        nPos := nPos + c_nOutputLimit; 
      END LOOP;
    END put_lines;
    I removed the exception handler, as it didn't seem to add any value.

  10. #10
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38
    Seems that I was carrying a few too many kilos.

    Thanks for the lesson.
    Cordialement

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Crassus
    Seems that I was carrying a few too many kilos.

    Thanks for the lesson.
    I think the motto "less is more" is very applicable to programming

  12. #12
    Join Date
    May 2003
    Posts
    87
    How about a shedding a few more ounces ??? ;-)

    Code:
    create or replace
    PROCEDURE put_lines (sText IN VARCHAR2)
    AS
      nPos PLS_INTEGER := 1;
      c_nOutputLimit CONSTANT PLS_INTEGER := 5;
    BEGIN
      WHILE (nPos <= LENGTH (sText))
      LOOP
        DBMS_OUTPUT.PUT_LINE (SUBSTR (sText, nPos, c_nOutputLimit));
        nPos := nPos + c_nOutputLimit; 
      END LOOP;
    END put_lines;
    /

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by dbmadcap
    How about a shedding a few more ounces ??? ;-)

    Code:
    create or replace
    PROCEDURE put_lines (sText IN VARCHAR2)
    AS
      nPos PLS_INTEGER := 1;
      c_nOutputLimit CONSTANT PLS_INTEGER := 5;
    BEGIN
      WHILE (nPos <= LENGTH (sText))
      LOOP
        DBMS_OUTPUT.PUT_LINE (SUBSTR (sText, nPos, c_nOutputLimit));
        nPos := nPos + c_nOutputLimit; 
      END LOOP;
    END put_lines;
    /
    That must be about perfect. However, I see that I accidentally changed the limit from 255 to 5 in my last edit!

  14. #14
    Join Date
    Feb 2003
    Location
    Paris
    Posts
    38
    Thank you, Jenny Craig. Wow! I feel great!
    Cordialement

  15. #15
    Join Date
    Sep 2008
    Posts
    1

    Arrow alternative checking out the chr(10)

    Hi all,

    I needed to have the chr(10) characters to be taken care of, and use them to cut down the string.

    I have found elsewhere (in Toad's Knowledge Expert for PL/SQL and here: http://utplsql.sourceforge.net/Archived/utplsql.pkb)

    It is recursive, and the error handling does add something here.


    Code:
      -- A local put_line to avoid DBMS_OUTPUT.PUT_LINE problems.
    create or replace
      PROCEDURE Put_Lines (
                  str       in  VARCHAR2,
                  len       in  INTEGER := 255,
                  expand_in in  BOOLEAN := TRUE
      )
      IS
        ln_len     PLS_INTEGER     := LEAST (len, 255);
        ln_len2    PLS_INTEGER;
        ln_chr10   PLS_INTEGER;
        lv_str     VARCHAR2 (4000);
      BEGIN
        IF LENGTH (str) > ln_len
        THEN
          ln_chr10 := INSTR (str, CHR(10));
          -- 
          IF ln_chr10 > 0 AND ln_len >= ln_chr10
          THEN
            ln_len := ln_chr10 - 1;
            ln_len2 := ln_chr10 + 1;
          ELSE
            ln_len2 := ln_len + 1;
          END IF;
    
          lv_str := SUBSTR (str, 1, ln_len);
          DBMS_OUTPUT.put_line (lv_str);
          Put_Lines (SUBSTR (str, ln_len2), len, expand_in);
        ELSE
          -- Save the string in case we hit an error and need to recover.
          lv_str := str;
          DBMS_OUTPUT.put_line (str);
        END IF;
      EXCEPTION
        WHEN OTHERS
        THEN
          /* TVP 9/99: Might want to use buffer size to STOP program */
          IF expand_in
          THEN
            DBMS_OUTPUT.ENABLE (1000000);
            DBMS_OUTPUT.put_line (lv_str);
          ELSE
            RAISE;
          END IF;
      END Put_Lines;
    /
    This is not very slim, but it is pretty good :-)
    Last edited by wlegoussouart; 09-05-08 at 01:58.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •