Print out a string longer than 255 characters in pl/sql



My OraFAQ Blog

Contact me

All versions of dbms_output.put_line before 10gR2 will choke if you pass in a string longer than 255 bytes. This simple print function takes a string that can be longer than 255 characters and splits it on word breaks (spaces) at the wraplength of your choice, then passes the resulting less-than-255-char lines to dbms_output.put_line .

(Of course, 1 byte = 1 character in USASCII7. I have set the maximum wraplength to 255 characters; if you are using a multibyte character set, adjust the maximum wraplength accordingly to keep dbms_output.put_line happy.)

procedure print ( mesg in varchar2, wraplength in number default null )
--Print out a string (can be longer than 255 characters)
  piece1 varchar2(4000);
  piece2 varchar2(4000);
  posn number ;
  wl number ;
  if wraplength is null then
  	wl := 255;
  	wl := least(wraplength,255);
  end if;

  if length(mesg) <= wl then
  	dbms_output.put_line ( mesg );
    piece1 := mesg ;
    posn := 1;
    while length(piece1) > 0 
    	posn := wl;
    	while substr(piece1,posn-1,1) not in (' ','	', chr(13),chr(9))
           posn := posn - 1;
           if posn = 1 then
            	posn := wl;
           end if;
         end loop;
  	 piece2 := substr(piece1, 1, posn-1);      
         dbms_output.put_line ( piece2 );
         piece1 := substr(piece1, posn, length(piece1));
  	 end loop;
	 end if;
	when others then
  	dbms_output.put_line ( '*** ERROR IN PRINT ***' );
    	dbms_output.put_line ( sqlerrm );
end print;

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...