SQL and PL/SQL Limitations

From: dxcreepin <dxcreepin_at_va.prestige.net>
Date: 16 Aug 2002 06:44:25 -0700
Message-ID: <5c3ada67.0208160544.2680be3e@posting.google.com>

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. Received on Fri Aug 16 2002 - 08:44:25 CDT