Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Ref cursor length limit...

RE: Ref cursor length limit...

From: Stephens, Chris <>
Date: Sun, 21 Nov 2004 15:12:13 -0500
Message-ID: <0C36D9C74ADA844292F3218A9C6345442B9541@exchange.pqa.local>

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


-----Original Message-----
From: Reidy, Ron []=20 Sent: Sunday, November 21, 2004 3:10 PM
To: Stephens, Chris; 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-----
[]On Behalf Of Stephens, Chris Sent: Sunday, November 21, 2004 1:06 PM
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)

	part1 varchar2(30000):=3D3D '1';
	part2 varchar2(30000):=3D3D '1';
	part3 varchar2(30000):=3D3D '1';
	counter number :=3D3D 0;
	rc1 rc;



			while counter < 1000 loop

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;
open rc1 for 'select count(*) from rep_year_dim
where ppl_id =3D3D



end tst_pkg;

Numeric or value error on the 'open...' Line.


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.

Received on Sun Nov 21 2004 - 14:09:31 CST

Original text of this message