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:
- The first parameter is the string you want to output.
- The seconde pamemeter is optional, if given, it define the length limit on each output line and it must be between 10 and 255.
- 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:
