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.
Received on Tue Aug 20 2002 - 07:52:17 CDT
