Over come the 255 Character Limit of DBMS_OUTPUT.PUT_LINE

255 charater Limitation with DBMS_OUTPUT.PUT_LINE

Oralce has provided the DBMS_OUTPUT package for the PL/SQL Developers. PUT_LINE function is probably the most used feature of Oracle Developers. PUT_LINE is used for displaying the results/outputs in the screen or for spool output. But this function has a restriction of 255 characters limit and when you try to go beyond this PL/SQL throws exception.

Refrer below example:

declare
   v_str varchar2(1000); 
begin 
 
   for i in 1..300 
   loop 
      v_str := v_str || TO_CHAR(i); 
   end loop; 
 
   dbms_output.put_line( v_str);
 
end; 
 
/ 

Above code would end up in error whowing below message:

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

How to over come this? Is there any solution?

Yes, we can solve this. The answer is split the string into chunks of 255 character (or less) and print them in a loop. But it will be a head ache putting this loop where ever we needed. So, it will be a nice idea if we put them in a stored procedure or package and call them.

Refer below stored procedure which wil have the logic for spliiting a long string and print them. This procedure takes the string to be printed as input.

create or replace procedure custom_output(in_string in varchar2 )
is 
 
   out_string_in long default in_string; 
   str_len number; 
   loop_count number default 0; 
 
begin 
 
   str_len := length(out_string_in);
 
   while loop_count < str_len
   loop 
      dbms_output.put_line( substr( out_string_in, loop_count +1, 255 ) ); 
      loop_count := loop_count +255; 
   end loop; 
 
end;
 
/ 

Run the below code which prints a string longer than 255, using above custom procedure you will see the code running without issues.

declare
 
   v_str varchar2(1000); 
 
begin 
 
   for i in 1..300 
   loop 
 
      v_str := v_str || TO_CHAR(i); 
 
   end loop; 
 
   custom_output( v_str);
 
end; 
 
/ 

We can use this in all places even if we donot have limitation issue. This will make sure the code will be smooth even if we modify a line which causes a 255 char limitation issue with DBMS_OUTPUT working fine so for.

Your comments and feedback will be appreciated.

Also read my hub pages on below topics.

More by this Author


Comments 4 comments

Hicham 7 years ago

Thanks a lot bro, you helped me!


Peter Williams 7 years ago

Great wee code snippet, thanks. I've modified it to detect linebreaks (chr 10) because I'm using it to output SQL ready for execute immediate and I've got a mix of long lines which need to be formatted, and some pre-formatting eg at known points such as 'Select', 'From' and 'Where' which I need to start their own lines.

Here is:

-- Thanks to http://hubpages.com/technology/Over-come-the-255-C... PROCEDURE Formatted_Output (in_string in varchar2, p_line_width NUMBER DEFAULT 100) IS out_string_in LONG DEFAULT in_string; str_len NUMBER; loop_count NUMBER DEFAULT 0; BEGIN --Does our output already contain a linebreak? Output up to the first linebreak --if so, then output the rest as well (will recursive work through all chr(10) instances). IF INSTR(in_string, chr(10), 1, 1) > 0 THEN Formatted_Output ( SUBSTR(in_string, 1, INSTR(in_string, chr(10), 1, 1)-1), p_line_width); Formatted_Output ( SUBSTR(in_string, INSTR(in_string, chr(10), 1, 1)+1), p_line_width); ELSE str_len := length(out_string_in); WHILE loop_count < str_len LOOP dbms_output.put_line( substr( out_string_in, loop_count +1, p_line_width ) ); loop_count := loop_count + p_line_width; END LOOP; END IF; END;


Megha 4 years ago

Hi selvirajan

This is very useful but the issue I have right now is I have to display 725 characters in one line. Your procedure displays only 255 characters in one line. the remaining are displayed in the next line.

Is there a way to display all the 725 characters in a single line?

Please help.

Thanks

Megha


selvirajan profile image

selvirajan 3 months ago from India Author

Catching up after so many years. Glad that helped few people and viewers added more value.

@Peter Williams ... Thanks for your code snippet too. Looks great.

@Megha, I am not sure any resolution to that. But, work around can be done. For example, you can store the out put to a temporary table and select / query output from that table. Will check and let you know if i can get any other better way.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working