Re: SQL and PL/SQL Limitations

In article <5c3ada67.0208211200.75198a88_at_posting.google.com>, dxcreepin_at_va.prestige.net says...
>
>Thomas Kyte <tkyte_at_oracle.com> wrote in message
>news:<ajtgnl01s7t_at_drn.newsguy.com>...
>> 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...
>> >> >
>
>[snip]
>
>> >> >
>> >> >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?
>
>Here is the slightly modified version of the function.
>create or replace function straggf( p_key_name in varchar2,
> p_key_val in varchar2,
> p_other_col_name in varchar2,
> p_tname in varchar2 )
> return clob
> as
> type rc is ref cursor;
> l_val varchar2(4000);
> l_cur rc;
> l_clob clob;
> 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;
> if ( l_clob is null )
> then
> dbms_lob.createTemporary( l_clob, FALSE );
> dbms_lob.writeAppend( l_clob, length(l_val), l_val );
> else
> dbms_lob.writeAppend( l_clob, length(l_val)+1, ','||l_val
>);
> end if;
> end loop;
> close l_cur;
> return l_clob;
>
> dbms_lob.freeTemporary ( l_clob);
>end;
>
>The results of my query, where I make 2 calls to this function, I get
>
>ERROR:
>ORA-22922: nonexistent LOB value
>
>This is after some processing has taken place. I get identical results
>if the lobs are cached or not cached. I get this error if it were to
>return mroe than 3 records. Is there any way around this? Also, is
>there a way to tune it (the function) so it is not as intensive on the
>temp space? Each time I run the query, my temp space decreases very
>rapidly. I would hate to see what happens in the production
>environment. Please advise.

I cannot reproduce the ora 22922 at all but I do see the temp issue. The temporary lobs are not being closed by sqlplus for example -- but your client code would be responsible for calling dbms_lob.freetemporary (your call above will NEVER be executed, it is after the return -- and if it WERE executed, it would be the cause of a 22922!)

Try having your client free the clob after its done with it.

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