Re: SQL and PL/SQL Limitations

dxcreepin wrote:

> 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.

VARCHAR2 variables can be up to 32K bytes. VARCHAR2 columns up to 4K bytes. You can't change that. But you can stuff the result set into a CLOB or parse it the way Oracle does when storing PL/SQL in user_source.

Daniel Morgan Received on Fri Aug 16 2002 - 12:22:21 CDT