Why not use global temporary tables in place of the list of id's? This = may help lower the potential hard parse count you are likely to = encounter. ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Stephens, Chris Sent: Sunday, November 21, 2004 1:06 PM To: oracle-l@xxxxxxxxxxxxx Subject: Ref cursor length limit... 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. =3D20 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 =3D3D 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 =3D09 part1 varchar2(30000):=3D3D '1'; part2 varchar2(30000):=3D3D '1'; part3 varchar2(30000):=3D3D '1'; =3D09 counter number :=3D3D 0; rc1 rc; =3D09 begin =3D20 while counter < 1000 loop =3D09 part1 :=3D3D part1 ||' or ppl_id =3D3D '||counter; part2 :=3D3D part2 ||' or ppl_id =3D3D '||counter; part3 :=3D3D part3 ||' or ppl_id =3D3D '||counter; =3D20 counter :=3D3D counter + 1; =3D20 end loop; =3D09 dbms_output.put_line(length(part1)); =3D09 open rc1 for 'select count(*) from rep_year_dim where ppl_id =3D3D '||part1||part2||part3||part1||part2||part3||part1||part2||part3; =3D09 end; end tst_pkg; / Numeric or value error on the 'open...' Line. -- //www.freelists.org/webpage/oracle-l This electronic message transmission is a PRIVATE communication which = contains information which may be confidential or privileged. The information is = intended=20 to be for the use of the individual or entity named above. If you are = not the=20 intended recipient, please be aware that any disclosure, copying, = distribution=20 or use of the contents of this information is prohibited. Please notify = the sender of the delivery error by replying to this message, or notify us = by telephone (877-633-2436, ext. 0), and then delete it from your system. -- //www.freelists.org/webpage/oracle-l