Re: Ref cursor length limit...
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 22 Nov 2004 00:33:00 +0000 (GMT)
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:
>
>
> 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. =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 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 =3D 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
> =09
> part1 varchar2(30000):=3D '1';
> part2 varchar2(30000):=3D '1';
> part3 varchar2(30000):=3D '1';
> =09
> counter number :=3D 0;
> rc1 rc;
> =09
> begin
> =20
> while counter < 1000 loop
> =09
> part1 :=3D part1 ||' or ppl_id =3D '||counter;
> part2 :=3D part2 ||' or ppl_id =3D '||counter;
> part3 :=3D part3 ||' or ppl_id =3D '||counter;
> =20
> counter :=3D counter + 1;
> =20
> end loop;
> =09
> dbms_output.put_line(length(part1));
> =09
> open rc1 for 'select count(*) from rep_year_dim
> where ppl_id =3D
> '||part1||part2||part3||part1||part2||part3||part1||part2||part3;
> =09
> end;
>
>
> end tst_pkg;
> /
>
>
> Numeric or value error on the 'open...' Line.
> --
> //www.freelists.org/webpage/oracle-l
>
=====
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"
------------------------------------------------------------
___________________________________________________________
Moving house? Beach bar in Thailand? New Wardrobe? Win £10k with Yahoo! Mail to
make your dream a reality.
Get Yahoo! Mail www.yahoo.co.uk/10k
--
//www.freelists.org/webpage/oracle-l
Other related posts:
