Re: Limitations of the Native dynamic Sql and Case expressions

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<qgkka.31125$Hx.22926914_at_twister.socal.rr.com>...
> Lolita wrote:
> <snip>
> > Is there any one who knows what is the longest character string I can
> > pass into an open for statement?
>
> Well, the longest 'string' you can pass is 32k since that's the limit on
> a varchar2 variable and you can't use a CLOB there. However, if you use
> a static statement instead of a string then the limit is much higher (I
> believe only bound by the size of the procedure you could compile).
>
> <snip>
> > I have tested the OPEN FOR statement with a select statement over than
> > 32KB and it worked.
>
> Are you sure this wasn't a static statement instead of a string? Can
> you post a snipet of the test code you used?
>
> <snip>
> > I have tested the case statement with over 128 choices and it worked.
> > Is there any one who have information about that?
>
> Indeed I did the same experiment with the CASE expression and it appears
> that the actual limit is 32,767 when/then pairs. I guess that's not the
> first time the documentation was wrong. Of course, if you do this and
> it stops working in the future they'll just say 'I told you so'.
> Perhaps the 255 number is some sort of SQL standard proposed limit they
> are referring to?
>
>
> Richard Kuhler

Thanks for your answers

Richard: Are you sure this wasn't a static statement instead of a string? Can
you post a snipet of the test code you used?

Lolita: You are right I have used a static statement instead of a string. I thougth I would still get an error message this way.

I did something like this: Open cursor for 'select * form test'; With a select statement much bigger than that.

I am happy to see you got similar results with the case expression.

Thanks
Lolita Received on Tue Apr 08 2003 - 06:06:16 CDT