| SQL and PL/SQL Limitations [message #39842] |
Fri, 16 August 2002 06:21  |
Damien
Messages: 3
Registered: August 2002
|
Junior Member |
|
|
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
