- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming
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.
