Re: ORA-01037: maximum cursor memory exceeded

Hi !

Just a couple of tips that may help, or may make it worse :

1. Change two of the tables you are checking with IN Like this: D.IndicatorCode IN -> I.Code IN D.CountryCode IN -> C.SDI_CCode IN
2. You are checking the codes against character-values, which forces Oracle to do implicit type-conversion. Could you rewrite something so that the user inputs becomes numbers, so that the IN-checkings will check against numbers (i.e. I.Code IN (4, 5, 6, 7). If you cant change this then maybe it will help to do this conversion yourself instead of leaving it to Oracle (i.e. To_Char(I.Code) IN ('4', '5', '6', '7')).

Best of luck.

Njål

Tom Parris wrote:
<had to delete a lot to send more reply than cite>

>
> All,
>
> I hope someone can help me with a problem I have been
> encountering with Oracle Server 7.3.3 on a Sun UltraSparc I
> with 128MB of RAM. I realize you all have many other fish
> to fry and am appreciative in advance of any help you can
> offer.
>
> The problem is that I have an application that has the
> potential of generating queries with up to three long IN
> (...) clauses. When these reach a certain theshold in
> length, I get the error:
>
> ORA-01037: maximum cursor memory exceeded
>
>
> The code lists are based on user input from checkboxes.
> Hence, I can't simply replace the "D.YearCode IN" clause
> with a range. The query works fine if I reduce the number
> of CountryCodes to 3 or 4. This is by no means the longest
> query possible.
 

> --
> Once again. Many many thanks for any help you can provide.
>
> -- Tom
> ____________________________________________________________
> Tom Parris, Environmental Resources Librarian,
  Received on Sat Jan 10 1998 - 00:00:00 CST