SQL and PL/SQL Limitations

[deecobbs]

Hello everyone.

I have a function that combines several rows of information for a particular column into one record to be returned to an SQL statement.

Table: INFO (all the columns are varchar(1) – in this example)
col1 col2 col3 col4 col5
a b c d d
a b c g
a b c i
1 2 3 4 5
1 2 3 a
1 2 3 d
z y x w v

would return this:

col1 col2 col3 col4 col5
a b c d dgi
1 2 3 4ad 5
z y x w v

The PL/SQL function can handle up to 32K characters but when I put the function call in a SQL select statement, it will only work with results less than 4000 characters. If the result returned from the function is greater than 4000 characters, I get this error message:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The result that is causing this error is about 10000 characters. Is there a way to circumvent this apparent 4000 character limit or am I doing something wrong? For more information, here is the function used to generate the results followed by a sample SQL statement showing syntax.

FUNCTION:
create or replace function unlimited_text ( p_key_name in varchar2,
p_key_val in varchar2,
p_other_col_name in varchar2,
p_tname in varchar2 )
return varchar2
as
type rc is ref cursor;
l_text varchar2(32767); -- max number allowed by oracle
l_val varchar2(32767);
l_cur rc;
begin

open l_cur for 'select '||p_other_col_name|| '
from '|| p_tname || '
where '|| p_key_name || ' = :x
and '||p_other_col_name|| ' is not null'
using p_key_val;

loop
fetch l_cur into l_val;
exit when l_cur%notfound;
l_text := l_text || l_val;

end loop;
close l_cur;


SQL Statement:
select distinct col1, col2, col3, unlimited_text('col1',col1,'col4','info') "col4", unlimited_text('col1',col1,'col5','info') "col5" from info;

Please help.

[TimHall]

You could try returning a CLOB rather than a VARCHAR2.