Re: SQL and PL/SQL Limitations

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

Thanks for the suggestion. I am not sure that Reports 6i can do that though (on a record basis). After the report has been completed, the extents in my temp tablespace are cleared up by the db, right? Throughout this post, I neglected to mention my db version: 8.1.5 (software package requirement). While searching metalink I came across this: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=112125.996

I suppose this explains why I get that message and you do not. I am actually accessing the data through a view (not through a db link however). Maybe I can write the results to a text file and pull the results from there to put in the report. Hmmm....

Thanks Tom for your help. Received on Thu Aug 22 2002 - 10:08:59 CDT