Unknown error! Help needed

Unknown error! Help needed

  
Hi there Mates.

Yesterday i've received the error that is mencioned in attached file and i don't know what to do.

Someone can tell me what's that error about?

Thanks in advance
Hi Pedro,

that is something you should address to support@outsystems.com

Thanks
RNA
Hi Pedro,

Indeed, Ricardo's reaction is mine as well. That kind of error should be communicated to our support department, especially if it is happening in a production environment.

You seem to have an Oracle installation, though, and that error is clearly database related. One pointer I may refer to is the following link

http://ora-01000.ora-code.com/

where they say:

ORA-01000: maximum open cursors exceeded
Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.
Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.


One more detailed workaround might be

http://www.orafaq.com/wiki/ORA-01000

Let us know if this helps!

Regards,

Paulo Tavares
Actually this error happened in a production environment!

I will try to report this situation to the Database Administrator.

Thanls
Is there any way to force a cursor to close?

Imagine that 1000 users are currently logged and working. It is impossible to manage 1000 users without closing automatically each one cursor.
I think that changing the OPEN_CURSORS parameters directly in ORACLE is not a good idea.

Regards,
Pedro Domingues
Hi Pedro.

Indeed, I'm not sure of how to automatically do that, but if it happened in a production environment, please get in touch with our support department as soon as possible, because they will be the ones to give you the guidelines to better set up your Oracle database infrastructure, because 1000 users does not seem like that much - we have installations far larger than that, so it would seem to me that it is either:

- a database configuration issue, or
- a bug in our Oracle implementation

The way to tackle any of those is to go report this to our support department.

Some questions that would be relevant would be to know if you're using the latest version of the Platform, for your specific major version (i.e. the latest 4.1.x, the latest 4.2.x or the latest 5.x), depending on your installation.

Let us know how this turns out!

Regards,

Paulo Tavares
Hi Paulo,

I've already submitted this error to the support team. I'm waiting feedback.

Thanks anyway.

Regards,
Pedro Domingues
Hi Pedro,

Looking at the error, this might indicate a database error because of using a faulty (heavy load) programming structure.

For every select you execute a cursor (record pointer) can be opened.
If the amount of recordpointers exceeds the database max. an error like this occurs.

I would put my questions to what creates the situation that you'd like to have more than 1000 cursors open.
  • Is there a part of your design that executes a lot of selects (e.g. in a For-Each)?
If this is the case; you might want to look at a different approach; e.g. by creating an Advanced query that would eliminate the For-Each.
Hi Eric,

Thanks for your reply.

In my applacation, it's possible to change/update a several amount of records (nearly 1000 probably) at the same time. That's my only issue about the amount of cursors in use. I think i cannot do it without foreach.

Thanks
Hi Pedro,

is there a key that is equal to every record? If so, instead doing a foreach record >> updateENTITY you should use a advanced query to update all the records at once.

RNA
Hi Pedro,

Try handling the updates as following; I think this might solve / evade the problem:

Create a 'shadow' table of the table(s) you wish to update.
  • Execute a SELECT FOR UPDATE in ONE query for all of the records you want to update.
  • Insert in the shadow table(s) all of the records you want to modify; using the ID's of the records you wish to update.
  • Execute one advanced query that will update the existing records based on the records in the shadow table(s).
  • DELETE all previous added data from the shadow table(s) if no database error has occurred and all is OK.
  • COMMIT your transaction
Pedro,

To be thinking of it; the main problem might be (besides the amount op open update cursors) the amount of open select cursors.

I'm not sure when Outsystems actually closes a cursor, but it might close the cursor if you put the select in a separate action that you call in the foreach.

Another way might be following:
Try to move the separate select create an advanced query that includes all of the ID's you want to retrieve.

I.E. in the foreach you now have:
SELECT *
FROM clients
WHERE id = [variable]


Move that select in front of the foreach and create a variable that contains all of the ID's you want to retrieve:
SELECT *
FROM clients
WHERE id IN ( [variable1], [variable2], [variable3], etc);


Now you can user the in-memory List to move to the correct record and then use it's data.
I'm not sure how quick these array actions are; but it will eliminate the amount of cursors being opened.

Although this might be an option. i'd look into a way to merge the inner selects with the outer select using an advanced query.

This might ofcourse be problematic if you use actions that determine your inner select queries.
In that case you could create a big advanced query instead of moving the inner selects to the foreach. (because that is probably the actual issue)

Cheers,

Eric