Ref cursor length limit...
- From: "Stephens, Chris" <ChrisStephens@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 21 Nov 2004 15:05:34 -0500
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
Other related posts:
