Limitation in dbms_output.put_line in pl/sql

by Vahid 18. February 2009 10:32
Today I was testing a function in pl/sql using pl/sql developer. In the function we are contenting so many strings that the leng of the final string would go more than 4000. As usual I was trying to use dbms_output.put_line to debug the function and make sure the end result is ok. But at the end of the function when I was trying to print the final string using this function I was not  getting the expected result. the string was being truncated. First I thought there is a problem with varchar2(4000) data type which I had declared for my variable. So I changed the data type to LONG which can hold unto 2GB of data. But still the same issue was there.Spending sometimes on the issue I thought there may be some limitation to the put_line function. So I created a temporary table with a single clob column and inserted the final string into that column instead of printing to out window. Surprisingly it worked properly and I was able to see the whole string from the column. Doing some R&D on this I came to know that dbms_output.put_line() function has just a buffer of 2000 characters and it will only print 2000 chars. I was not able to find out any way to increase this buffer size but once I find it, I’ll write about it. 


Tags: , ,