Re: SQL and PL/SQL Limitations

From: dxcreepin <dxcreepin_at_va.prestige.net>
Date: 21 Aug 2002 13:00:47 -0700
Message-ID: <5c3ada67.0208211200.75198a88@posting.google.com>

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. Received on Wed Aug 21 2002 - 15:00:47 CDT