Print large string in PL/SQL.

For PL/SQL developers put_line is a quite useful method. I use it for logging purposes. Recently I found out that the method has an annoying limitation. You cannot print more than 255 characters per line!
Now that is a strange feature. I don’t know what is the reasoning behind this restriction but it does make this method somewhat risky to use. Because if you use put_line to log a string more than 255 characters long it will raise an exception and you program might fail. Unless you catch the exception and continue on with your flow. Obviously no good programmer would want the trouble of adding exception handling code for such a mundane task as logging. So far I have not found an alternative method to print out large string in PL/SQL but I came up with a work around.
I created a little procedure to print large string.


Here is the method.

PROCEDURE print_line(p_str IN VARCHAR2) IS
l_offset NUMBER;
l_length NUMBER;
l_max_len NUMBER;
l_line VARCHAR2(256);
BEGIN
 l_offset :=1;
 l_max_len :=255;
 l_length:=LENGTH(p_str);
dbms_output.put_line(l_length);
 WHILE l_offset <= l_length LOOP
	   l_str := SUBSTR(p_str,  l_offset,
	                         l_max_len);
	   l_offset := l_offset+l_max_len;
      dbms_output.put_line(l_line);
    END LOOP;
 END;

Let's now write a simple test client to see this method in action.

DECLARE
l_str VARCHAR2(3000);
l_phrase VARCHAR2(300);
BEGIN
l_phrase :=' The quick brown fox jumps over the lazy dog';
--Create a string bigger than 255 characters. 
FOR i IN 1..20 LOOP
 l_str := l_str || l_phrase;
END LOOP;
l_length:=LENGTH(l_str);
dbms_output.put_line(l_length); -- The string has 880 characters.
--dbms_output.put_line(l_str); //Cannot do this. You will get following error.
/*
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 15
*/
 --Now try our method.
  print_line(l_str);
END;

Here is the output
/*
The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the
lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps
over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown
fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog
*/


Feel free to copy, modify and use this method. If you know a better way of achieving this please let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *