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