Re: SQL and PL/SQL Limitations

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<ajjcgv017s0_at_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
>

Yes Tom... very familiar ;) The solution was exactly what we needed at the time but now the requirements have changed slightly.

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

When I try to run my SQL statement now with the functions and types above, I get:
ORA-00932: inconsistent datatypes

Am I doing something wrong? Received on Mon Aug 19 2002 - 09:37:42 CDT