web analytics
An Alternative for Oracle DBMS_OUTPUT.PUT_LINE Without 255 Characters Limitation Options
codeling
Posted: Tuesday, October 17, 2017 1:13:06 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 982
Points: 4134

DBMS_OUTPUT.PUT_LINE allows you to write information to a buffer throughout the execution of a trigger/procedure. That information is available to be read by a trigger/procedure (using GET_LINE(S)), or dumped to SQL*Plus upon completion of execution.

In Oracle Database 10g Release 1 and earlier, the DBMS_OUTPUT.PUT_LINE were constrained by the 255 byte limit, that means you can not display a string with more than 255 characters.

The following stored procedure breaks the 255 characters barrier imposed by DBMS_OUTPUT.PUT_LINE. This stored procedure accepts three parameters:

  1. The first parameter is the string you want to output.
  2. The seconde pamemeter is optional, if given, it define the length limit on each output line and it must be between 10 and 255.
  3. The third one is optional too, it is used to define the delimiters to split the long string. By default, it could be newline, space, tab or comma.

Stored Procedure put_long_line

CREATE OR REPLACE PROCEDURE put_long_line(
    Ptext IN VARCHAR2,
    Plen  IN NUMBER DEFAULT 80,
    Pwhsp IN VARCHAR2 DEFAULT  -- newline+space+tab+comma
             CHR(10) || CHR(32) || CHR(9) || ',')

IS

    NL CONSTANT VARCHAR2(1) := CHR(10);  -- newline character (OS-independent)
    SP CONSTANT VARCHAR2(1) := CHR(32);  -- space character
    TB CONSTANT VARCHAR2(1) := CHR(9);   -- tab character
    CM CONSTANT VARCHAR2(1) := ',';      -- comma
    start_pos INTEGER := 1;  -- start of string to print
    stop_pos  INTEGER;       -- end of substring to print
    done_pos  INTEGER := LENGTH(Ptext);  -- end of string to print
    nl_pos    INTEGER;       -- point where newline found
    len       INTEGER := GREATEST(LEAST(Plen, 255), 10);  -- 10 <= len <= 255!

BEGIN

    --DBMS_OUTPUT.put_line('DONE_POS=' || TO_CHAR(done_pos));
    --DBMS_OUTPUT.put_line('LEN=' || TO_CHAR(len));
    IF (done_pos <= len) THEN  -- short enough to write in one chunk

      DBMS_OUTPUT.put_line(Ptext);

    ELSE  -- must break up string

      WHILE (start_pos <= done_pos) LOOP
        nl_pos := INSTR(SUBSTR(Ptext, start_pos, len), NL) + start_pos - 1;
        --DBMS_OUTPUT.put_line(SUBSTR(Ptext, start_pos, len));
        --DBMS_OUTPUT.put_line('NL_POS=' || TO_CHAR(nl_pos));
        IF (nl_pos >= start_pos) THEN  -- found a newline to break on
          DBMS_OUTPUT.put_line(SUBSTR(Ptext, start_pos, nl_pos-start_pos));
          start_pos := nl_pos + 1;  -- skip past newline
          --DBMS_OUTPUT.put_line('1.START_POS=' || TO_CHAR(start_pos));
        ELSE  -- no newline exists in chunk; look for whitespace
          stop_pos := LEAST(start_pos+len-1, done_pos);  -- next chunk not EOS
          --DBMS_OUTPUT.put_line('1.STOP_POS=' || TO_CHAR(stop_pos));
          IF (stop_pos < done_pos) THEN  -- intermediate chunk
            FOR i IN REVERSE start_pos .. stop_pos LOOP
              IF (INSTR(Pwhsp, SUBSTR(Ptext, i, 1)) != 0) THEN
                stop_pos := i;  -- found suitable break pt
                --DBMS_OUTPUT.put_line('2.STOP_POS=' || TO_CHAR(stop_pos));
                EXIT;  -- break out of loop
              END IF;
            END LOOP;  -- find break pt
          ELSE  -- this is the last chunk
            stop_pos := stop_pos + 1;  -- point just past EOS
            --DBMS_OUTPUT.put_line('3.STOP_POS=' || TO_CHAR(stop_pos));
          END IF;  -- last chunk?
          DBMS_OUTPUT.put_line(SUBSTR(Ptext, start_pos, stop_pos-start_pos+1));
          start_pos := stop_pos + 1;  -- next chunk
          --DBMS_OUTPUT.put_line('2.START_POS=' || TO_CHAR(start_pos));
        END IF;  -- find newline to break on
      END LOOP;  -- writing chunks

    END IF;  -- short enough or break up string?

END put_long_line;
/

Examples

The following PL/SQL statement outputs 800 '*' characters:

begin
     put_long_line(rpad('*',800,'*'));
end;

By default, the put_long_line will split the 800 '*' characters into 10 lines, each line has 80 '*' characters:

 

If you want to each row has 100 '*' characters, the you should assign the value to the second parameter:

begin
     put_long_line(rpad('*',800,'*'), 100);
end;

the put_long_line will split the 800 '*' characters into 8 lines, , each line has 100 '*' characters:

 

Sponsor
Posted: Tuesday, October 17, 2017 1:13:06 PM
codeling
Posted: Tuesday, October 17, 2017 1:15:19 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 982
Points: 4134

DBMS_OUTPUT has been upgraded since Oracle Database 10g Release 2. Not only can you specify a buffer size of unlimited (no more 1,000,000-byte limit!), but you can also print lines much larger than 255 characters. The line length limit is now 32K. The following demonstrates the DBMS_OUTPUT upgrade:

SQL> set serveroutput on size unlimited

SQL> begin
  2  dbms_output.put_line
  3  ( rpad('*',2000,'*') );
  4  end;
  5  /
codeling
Posted: Thursday, October 19, 2017 2:20:35 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 982
Points: 4134

Another verison of put_long_line.

CREATE OR REPLACE PROCEDURE put_long_line(
 str         IN   VARCHAR2,
 len         IN   INTEGER := 80,
 expand_in   IN   BOOLEAN := TRUE
)
IS
   v_len     PLS_INTEGER     := LEAST (len, 255);
   v_len2    PLS_INTEGER;
   v_chr10   PLS_INTEGER;
   v_str     VARCHAR2 (2000);
BEGIN
   IF LENGTH (str) > v_len
   THEN
      v_chr10 := INSTR (str, CHR (10));

      IF v_chr10 > 0 AND v_len >= v_chr10
      THEN
         v_len := v_chr10 - 1;
         v_len2 := v_chr10 + 1;
      ELSE
         v_len2 := v_len + 1;
      END IF;

      v_str := SUBSTR (str, 1, v_len);
      DBMS_OUTPUT.put_line (v_str);
      my_putline (SUBSTR (str, v_len2), len, expand_in);
   ELSE
      -- Save the string in case we hit an error and need to recover.
      v_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 (v_str);
      ELSE
         RAISE;
      END IF;
END put_long_line;
/

Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2018 Digcode.com. All rights reserved.