Re: SQL and PL/SQL Limitations

In article <5c3ada67.0208200452.535e6545_at_posting.google.com>, dxcreepin_at_va.prestige.net says...
>
>Thomas Kyte <tkyte_at_oracle.com> wrote in message
>news:<ajrav40rpn_at_drn.newsguy.com>...
>> In article <5c3ada67.0208190637.7671b675_at_posting.google.com>,
>> dxcreepin_at_va.prestige.net says...
>> >
>> >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...
>> >> >
>>
>>
>> ....
>>
>> >>
>> >> 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?
>>
>>
>>Example please -- cut and paste from sqlplus. include server versions and such
>> as well.
>>
>> I tested only on 817 and 9i r1/r2.
>
>I figured out the problem. I was using the wrong function... silly me.
>I works as well as can be expected now, aside from the 4000 character
>SQL limit. Is there a way around that using Reports 6i? Ultimately
>this query will be used in Reports 6i.

Confusion here -- the example above has a 4gig sql limit, not 4000?

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