RE: Ref cursor length limit...
- From: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>
- To: "Stephens, Chris" <ChrisStephens@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 21 Nov 2004 13:15:42 -0700
1. Because the current solution will never scale.
2. 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.0406291=
230.3f8e787a%40posting.google.com&rnum=3D5)
-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: Stephens, Chris [mailto:ChrisStephens@xxxxxxx]
Sent: Sunday, November 21, 2004 1:12 PM
To: Reidy, Ron; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx]=20
Sent: Sunday, November 21, 2004 3:10 PM
To: Stephens, Chris; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Stephens, Chris
Sent: Sunday, November 21, 2004 1:06 PM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
