RE: Ref cursor length limit...

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Sun, 21 Nov 2004 13:10:27 -0700
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B4390DA@fiji.arraybp.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Stephens, Chris Sent: Sunday, November 21, 2004 1:06 PM
To: oracle-l_at_freelists.org
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.

--
http://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.

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

Received on Sun Nov 21 2004 - 14:05:58 CST