Ref cursor length limit...

From: Stephens, Chris <ChrisStephens_at_pqa.com>
Date: Sun, 21 Nov 2004 15:05:34 -0500
Message-ID: <0C36D9C74ADA844292F3218A9C6345442B9540@exchange.pqa.local>

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.

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Nov 21 2004 - 14:02:24 CST