Re: Limitations of the Native dynamic Sql and Case expressions

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 Received on Mon Apr 07 2003 - 21:25:10 CEST