Re: SQL and PL/SQL Limitations

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.

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