| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Ref cursor length limit...
-----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]=20
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.
-----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;
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
=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=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-lReceived on Sun Nov 21 2004 - 14:12:13 CST
 |
 |