Error: Maximum Open Cursors Exceeded

[michaelgray]

I get the above error when running the following SQL query to an Aspect ACD Database running Oracle 8i.

The error also mentions: ORA02063 - preceding line from HACC_LINK.

SQL Statement which produces this error (Note this has been changed to Select * as the actual statement is quite lengthy)

SELECT
CALLDETAIL.*, APPLIC.*
FROM
"ACC".CALLDETAIL,
"ACC".APPLIC
WHERE
CALLDETAIL."APPLIC_NUM" = APPLIC."AP_NUM" AND
CALLDETAIL."TERM_DATE" = 1031009. AND
(APPLIC."AP_NUM" = 2. OR
APPLIC."AP_NUM" = 10. OR
APPLIC."AP_NUM" = 16. OR
APPLIC."AP_NUM" = 18. OR ...... so on

There are around 200 "AP_NUMS" that it selects from.

This error is derived from a Crystal Report, which selects ALL "App_Nums", but then the report mines the actual numbers it needs based on a Grouping in "Specific Order".

Unfortunatally the database is maintained by the Aspect vendor, and I have zero control on that server for settings etc.

Can anybody shed some light on this for me!?

 

[uparna]

Hi Michael ,
Have you tried using the "IN" clause for the query rather than your OR statements ? For eg :

SELECT CALLDETAIL.*, APPLIC.*
FROM "ACC".CALLDETAIL, "ACC".APPLIC
WHERE CALLDETAIL."APPLIC_NUM" = APPLIC."AP_NUM" AND
CALLDETAIL."TERM_DATE" = 1031009. AND
APPLIC."AP_NUM" IN ( 2,10,16,18....) ;
I am not sure this would help , but it is worth a shot, dont you think ?
Moreover , a single SQL does not give rise to the maximum number of Cursors error. A Procedure or a similar program usually does. Provide more information...

Regards,
S. Jayaram Uparna .

 

[karluk]

If you can't modify the SQL that is producing this error, you should talk to your DBA and see if he will increase the value of the OPEN_CURSORS startup parameter. That will increase the maximum number of cursors in a session, and may fix the problem.