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: , ,


Sort based on input parameter in oracle query.

by Vahid 10. January 2009 09:15
Today I was tracing a defect happened in a stored procedure with paging functionality. After spending sometimes on it I came to know that sorting does not work properly. In the order by clause of the procedure we were using an input parameter which holds the column name based on which the result was to be sorted. But it seems that oracle and pl-sql does not support this feature and if we want to sort based on an input parameter we have to go for dynamic query which I don’t like at all.But it's funny that the feature is not there in oracle because the same thing can be done in ms sql server easily.Anyone got some idea about this, please let me know.


Tags: ,