Over come the 255 Character Limit of DBMS_OUTPUT.PUT_LINE
By selvirajan
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.
Comments
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/hub/Over-come-the-255-Charater 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;
Hicham 3 years ago
Thanks a lot bro, you helped me!