Re: SQL and PL/SQL Limitations

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 16 Aug 2002 10:24:47 -0700
Message-ID: <ajjcgv017s0@drn.newsguy.com>

In article <5c3ada67.0208160544.2680be3e_at_posting.google.com>, dxcreepin_at_va.prestige.net says...
>
>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.

Hey, that looks familar ;)
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2200571416651

anyway -- in SQL varchar2 is 4000 bytes at most. You will not exceed that limit when using SQL. So, we need to move onto a CLOB. Here is a 9i aggregate function or an 8i and before "hack" to achieve something similar:

ops$tkyte_at_ORA9I.WORLD> create or replace type string_agg_type as object   2 (

  3     total clob,
  4     n     number,
  5  
  6     static function
  7          ODCIAggregateInitialize(sctx IN OUT string_agg_type )
  8          return number,
  9  
 10     member function
 11          ODCIAggregateIterate(self IN OUT string_agg_type ,
 12                               value IN varchar2 )
 13          return number,
 14  
 15     member function
 16          ODCIAggregateTerminate(self IN string_agg_type,
 17                                 returnValue OUT  clob,
 18                                 flags IN number)
 19          return number,
 20  
 21     member function
 22          ODCIAggregateMerge(self IN OUT string_agg_type,
 23                             ctx2 IN string_agg_type)
 24          return number

 25 );
 26 /

Type created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> create or replace type body string_agg_type   2 is
  3
  4 static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)   5 return number
  6 is
  7 l_clob clob;
  8 begin

  9      dbms_lob.createTemporary( l_clob, TRUE );
 10      sctx := string_agg_type( l_clob, 0 );
 11      return ODCIConst.Success;

 12 end;
 13
 14 member function ODCIAggregateIterate(self IN OUT string_agg_type,

 15                                       value IN varchar2 )

 16 return number
 17 is
 18 begin

 19       if ( n > 0 )
 20      then
 21          dbms_lob.writeAppend( self.total, length(value)+1, ',' || value );
 22      else
 23          n := 1;
 24          dbms_lob.writeAppend( self.total, length(value), value );
 25      end if;
 26      return ODCIConst.Success;

 27 end;
 28
 29 member function ODCIAggregateTerminate(self IN string_agg_type,

 30                                         returnValue OUT clob,
 31                                         flags IN number)

 32 return number
 33 is
 34 begin

 35      returnValue := self.total;
 36      return ODCIConst.Success;

 37 end;
 38
 39 member function ODCIAggregateMerge(self IN OUT string_agg_type,

 40                                     ctx2 IN string_agg_type)

 41 return number
 42 is
 43 begin

 44      dbms_lob.append( self.total, ctx2.total );
 45      return ODCIConst.Success;

 46 end;
 47
 48
 49 end;
 50 /

Type body created.

ops$tkyte_at_ORA9I.WORLD> show err
No errors.
ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> CREATE or replace   2 FUNCTION stragg(input varchar2 )
  3 RETURN clob
  4 PARALLEL_ENABLE AGGREGATE USING string_agg_type;   5 /

Function created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> REM -- for display purposes in sqlplus, use set long to control

ops$tkyte_at_ORA9I.WORLD> REM -- the amount of a CLOB/LONG to show
ops$tkyte_at_ORA9I.WORLD> set long 10
ops$tkyte_at_ORA9I.WORLD> 
ops$tkyte_at_ORA9I.WORLD> select deptno, stragg(rpad( ename, 4000, '*' )),
  2             dbms_lob.getlength( stragg(rpad( ename, 4000, '*' )) ) len

  3 from scott.emp
  4 group by deptno
  5 /

    DEPTNO STRAGG(RPA LEN
---------- ---------- ----------

        10 CLARK*****      12002
        20 SMITH*****      20004
        30 ALLEN*****      24005

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> create or replace

  2  function straggf( p_key_name in varchar2,
  3                    p_key_val  in varchar2,
  4                    p_other_col_name in varchar2,
  5                    p_tname     in varchar2 )

  6 return clob
  7 as

  8      type rc is ref cursor;
  9      l_val    varchar2(4000);
 10      l_cur    rc;
 11      l_clob   clob;

 12 begin
 13

 14      open l_cur for 'select '||p_other_col_name||'
 15                        from '|| p_tname || '
 16                       where '|| p_key_name || ' = :x
 17                         and '||p_other_col_name|| ' is not null'
 18                  using p_key_val;
 19  
 20      loop
 21          fetch l_cur into l_val;
 22          exit when l_cur%notfound;
 23          if ( l_clob is null )
 24          then
 25              dbms_lob.createTemporary( l_clob, TRUE );
 26              dbms_lob.writeAppend( l_clob, length(l_val), l_val );
 27          else
 28              dbms_lob.writeAppend( l_clob, length(l_val)+1, ','||l_val );
 29          end if;
 30      end loop;
 31      close l_cur;
 32  
 33      return l_clob;

 34 end;
 35 /

Function created.

ops$tkyte_at_ORA9I.WORLD> select deptno, straggf( 'deptno', deptno, 'rpad(ename,4000,''*'')', 'scott.emp' ), 2 dbms_lob.getlength(straggf( 'deptno', deptno, 'rpad(ename,4000,''*'')', 'scott.emp' )) len   3 from (select distinct deptno

  4            from scott.emp
  5         )

  6 /

    DEPTNO STRAGGF('D LEN
---------- ---------- ----------

        10 CLARK*****      12002
        20 SMITH*****      20004
        30 ALLEN*****      24005

ops$tkyte_at_ORA9I.WORLD>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Aug 16 2002 - 12:24:47 CDT