ORA-01000: maximum open cursors exceeded

At a glance, seems like an cursor has not been closed in a loop. Can you share more details of the the error as in logs or debug notes?

- Nalaka - 

Hi @hlhasanga 

Are you getting this error message in  Batch Print Customer Invoices window ?

I observed a support case regarding the same error message,  and it has suggested the below workaround. You may try it.

“This  issue can be due to high number of invoices that you are trying to print at once.
Please try to filter down the number of records printing at once by giving values to the parameters given  in the Batch print window.”

Hi @hlhasanga,

In my opinion, the issue occurs when the opened DB cursors surpass the max value for the database. In order to fix this you will need to increase the max value.

Best Regards,
Thanushi

There may be various reasons for the above error. Sometimes the open cursor does not closing and loop is running without closing the cursor in the database level source code.  What kind of invoices you are going to Print?

Hi,

Like @Nalaka Samarasinghe mentioned, usually, this issue occurs when a cursor which is open but not closed during loop execution. However, this can occur when a recursive method (where a cursor exists) call exists inside a loop as well.

As @Thanushi Jayaweera mentioned the easiest solution is to increase the number of Open Cursors. This can be achieved by the below statement:

ALTER SYSTEM SET open_cursors = <VALUE_HIGHER_THAN_EXISTING> SCOPE=BOTH;

However, if there is a high data load, this can lead to performance degradation in the system. Hence if this option doesn’t resolve the issue, kindly raise a new case to IFS Support.

Hi @hlhasanga,

As mentioned above, root cause of this issue is an open cursor which is not closed after fetching the results and if you are not from a technical background, CURSOR is something we use to fetch a records set from database side to process further using a select query and it is mandatory to close the cursor once the record set is fetched.

Anyway we have just gone through a knowledge search and couldn't find any known bug which throws this error when print invoices in IFSAPP 9 (you can check what @Dismanthi Dissanayaka suggested as well)

There is an event action call 'PDF_REPORT_CREATED'  which is triggered when we print invoices and may be within this event action there might be a custom code with this open cursor which throws this error. So you can check this in window event actions and then search for  event  'PDF_REPORT_CREATED' and ‘enabled’ flag as True and it will show you number of active event actions and just navigate to those event actions using RMB ‘Show Details’ and check whether any of these event actions are defined with sql statement and if you can find sql statement field has a implementation then just check whether there is any code block start with CURSOR and if so you can disable this event action and just check whether issue is solved (If you check this in a production environment, relevant event will not be triggered if you disable it and in that case do not disable it) If issue solved by disabling this event action then just send the whole sql statement and we will analyze it and provide you the modification by closing the relevant cursor.

In case this is not the reason for this issue you can send a case to GSD for further investigation.

Thank You and Best Regards,

Dammika

it looks like a performance issue, please see the error text 

We can see the PDF in the print manager.Any help would be much appreciated 

This situation can happen due to various reasons such as ,

invalid coding , corrupted data , surpassing database maximum restriction can be some of them . What I would recommend is to first investigate the issue from a technical perspective before increasing the maximum restriction .

because there is a reason to have a standard restriction and skipping it up without a proper inspection can lead up to severe side effects .

Hi @hlhasanga,

Considering this error text, it’s better if you could get this issue technically investigated from IFS foundation end first. 

Kind regards,