RE: Ref cursor length limit...
- From: "Stephens, Chris" <ChrisStephens@xxxxxxx>
- To: <hamcdc@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 22 Nov 2004 07:23:20 -0500
Yeah, I was thinking about this last night. We actually did try using =
pl/sql tables to prevent all the parses earlier but were getting =
horrible execution plans. I seem to remember an 'inlist pickler =
fetcher' operation or something to that affect. ...basically it executes =
the entire query as if the in list did not exist and then applies the in =
list filter in the final step of the plan regardless of how selective it =
is.
So there are hints to possible get the optimize to use the in list =
filters earlier?=20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Connor McDonald
Sent: Sunday, November 21, 2004 7:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Ref cursor length limit...
You're caught between the proverbial rock and hard place...
a) use massive SQL stmts
- you'll hit some sort of limit (perhaps IN-list items, total statement =
length, big parse times
etc)
b) use temp tables
- got some redo issues there in most releases
c) use PL/SQL tables
- possible dubious explain plans
If you opt for (c), you might want to architect in some CARDINALITY =
hints et al to give the optimizer some better clues
hth
connor
--- "Stephens, Chris" <ChrisStephens@xxxxxxx> wrote:=20
>=20
>=20
> We have a situation where we are generating a ref cursor based on a=20
> set of strings passed into a package. The problem is that one of=20
> those strings is a list of id's that could potentially be larger than=20
> the length limit of varchar2 so someone here decided to change the=20
> datatypes to clobs. =3D20
>=20
> 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=20
> concatenation of those strings.
>=20
> ...except I believe there is a limit (32k) on the length of a sql=20
> statement that a ref cursor can open up correct?
>=20
> So I want to:
>=20
> Open rc for varcharString1 || varcharString2 || ....;
>=20
> ...and all the varchar strings will potentially be =3D3D 32k (or just=20
> under).
>=20
> 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?
>=20
>=20
> ...i wrote what I believe to be a test but I don't think the error=20
> message is going to be convincing enough:
>=20
>=20
> CREATE OR REPLACE package body tst_pkg is
>=20
> 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=20
> '||part1||part2||part3||part1||part2||part3||part1||part2||part3;
> =3D09
> end;
>=20
>=20
> end tst_pkg;
> /
>=20
>=20
> Numeric or value error on the 'open...' Line.
> --
> //www.freelists.org/webpage/oracle-l
> =20
=3D=3D=3D=3D=3D
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, =
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
=09
___________________________________________________________
Moving house? Beach bar in Thailand? New Wardrobe? Win =A310k with =
Yahoo! Mail to make your dream a reality.=20
Get Yahoo! Mail www.yahoo.co.uk/10k
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
