when you have and COL in ( select X from table(plsql_table_type) ) you might want to consider the cardinality hint in the sub query so that Oracle knows how rows in the PL/SQL table are expected and can (hopefully) optimize accordingly. Alternatively, you could try the WITH clause to lean Oracle toward evaluating that stuff first into temp tables. hth connor --- "Stephens, Chris" <ChrisStephens@xxxxxxx> wrote: > Yeah, I was thinking about this last night. We actually did try using pl/sql > tables to prevent > all the parses earlier but were getting horrible execution plans. I seem to > remember an 'inlist > pickler fetcher' operation or something to that affect. ...basically it > executes the entire > query as if the in list did not exist and then applies the in list filter in > the final step of > the plan regardless of how selective it is. > > So there are hints to possible get the optimize to use the in list filters > earlier? > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On > Behalf Of Connor > McDonald > Sent: Sunday, November 21, 2004 7:33 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: Ref cursor length limit... > > You're caught between the proverbial rock and hard place... > > a) use massive SQL stmts > - you'll hit some sort of limit (perhaps IN-list items, total statement > length, big parse times > etc) > > b) use temp tables > - got some redo issues there in most releases > > c) use PL/SQL tables > - possible dubious explain plans > > If you opt for (c), you might want to architect in some CARDINALITY hints et > al to give the > optimizer some better clues > > hth > connor > > --- "Stephens, Chris" <ChrisStephens@xxxxxxx> wrote: > > > > > > We have a situation where we are generating a ref cursor based on a > > set of strings passed into a package. The problem is that one of > > those strings is a list of id's that could potentially be larger than > > the length limit of varchar2 so someone here decided to change the > > datatypes to clobs. =20 > > > > Once I get the clob, I am supposed to break it down into a set of > > varchar2 strings and then open the ref cursor based on the > > concatenation of those strings. > > > > ...except I believe there is a limit (32k) on the length of a sql > > statement that a ref cursor can open up correct? > > > > So I want to: > > > > Open rc for varcharString1 || varcharString2 || ....; > > > > ...and all the varchar strings will potentially be =3D 32k (or just > > under). > > > > I've been searching the documention for this limit but I cannot find it. > > ....anyone know where I can get this so I can show my manager? > > > > > > ...i wrote what I believe to be a test but I don't think the error > > message is going to be convincing enough: > > > > > > CREATE OR REPLACE package body tst_pkg is > > > > procedure tst(v_record_set out rc) > > is > > =09 > > part1 varchar2(30000):=3D '1'; > > part2 varchar2(30000):=3D '1'; > > part3 varchar2(30000):=3D '1'; > > =09 > > counter number :=3D 0; > > rc1 rc; > > =09 > > begin > > =20 > > while counter < 1000 loop > > =09 > > part1 :=3D part1 ||' or ppl_id =3D '||counter; > > part2 :=3D part2 ||' or ppl_id =3D '||counter; > > part3 :=3D part3 ||' or ppl_id =3D '||counter; > > =20 > > counter :=3D counter + 1; > > =20 > > end loop; > > =09 > > dbms_output.put_line(length(part1)); > > =09 > > open rc1 for 'select count(*) from rep_year_dim where > > ppl_id =3D > > '||part1||part2||part3||part1||part2||part3||part1||part2||part3; > > =09 > > end; > > > > > > end tst_pkg; > > / > > > > > > Numeric or value error on the 'open...' Line. > > -- > > //www.freelists.org/webpage/oracle-l > > > > ===== > Connor McDonald > Co-author: "Mastering Oracle PL/SQL - Practical Solutions" > ISBN: 1590592174 > > web: http://www.oracledba.co.uk > web: http://www.oaktable.net > email: connor_mcdonald@xxxxxxxxx > > Coming Soon! "Oracle Insight - Tales of the OakTable" > > "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, > and...he will sit in a > boat and drink beer all day" > > ------------------------------------------------------------ > > > > ___________________________________________________________ > Moving house? Beach bar in Thailand? New Wardrobe? Win £10k with Yahoo! Mail > to make your dream > a reality. > Get Yahoo! Mail www.yahoo.co.uk/10k > -- > //www.freelists.org/webpage/oracle-l > ===== Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk web: http://www.oaktable.net email: connor_mcdonald@xxxxxxxxx Coming Soon! "Oracle Insight - Tales of the OakTable" "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" ------------------------------------------------------------ ___________________________________________________________ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- //www.freelists.org/webpage/oracle-l