RE: Ref cursor length limit...

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

So (following the link) it's a sql statement length limit and not a ref cursor limit?=20

-----Original Message-----

From: Reidy, Ron [mailto:Ron.Reidy_at_arraybiopharma.com]=20 Sent: Sunday, November 21, 2004 3:16 PM
To: Stephens, Chris; oracle-l_at_freelists.org Subject: RE: Ref cursor length limit...

Because the current solution will never scale.
Because the max length is limited (http://groups.google.com/groups?q=3Dmax+length+of+sql+statement&hl=3Den&= lr=3D &group=3Dcomp.databases.oracle.*&scoring=3Dd&selm=3Db3cb12d6.0406291230.3= f8e78 7a%40posting.google.com&rnum=3D5)

Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----

From: Stephens, Chris [mailto:ChrisStephens_at_pqa.com] Sent: Sunday, November 21, 2004 1:12 PM
To: Reidy, Ron; oracle-l_at_freelists.org
Subject: RE: Ref cursor length limit...

That's actually the proposed solution to the problem. ...but I need a good reason to change the app this late in the game.

chris=20

-----Original Message-----

From: Reidy, Ron [mailto:Ron.Reidy_at_arraybiopharma.com] Sent: Sunday, November 21, 2004 3:10 PM
To: Stephens, Chris; oracle-l_at_freelists.org Subject: RE: Ref cursor length limit...

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 to be for the use of the individual or entity
named above. If you are not the intended recipient, please be aware that
any disclosure, copying, distribution 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.


This electronic message transmission is a PRIVATE communication which
contains information which may be confidential or privileged. The
information is intended to be for the use of the individual or entity
named above. If you are not the intended recipient, please be aware that
any disclosure, copying, distribution 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:15:09 CST