Oracle Open cursors, web client, and media objects

Hi List,

For some time, on our SP23 production platform, we sometimes get issues due to the "ORA-01000 : maximum open cursors exceeded" Oracle error, especially at this time of year whish is our peak period.
Few years ago, we had "open_cursors" parameter set to 800, but due to some specific ube (with some specific orders we had couples of time per year) that failed because of
this error, we increased it to 1500.
But now we get the error from the web client (jas on websphere).

So i started to monitor open cursors on the database to try to see what kind of process was getting the error, and why it was opening so many cursors.
So far, i couldn't see any oracle session reaching the limit of 1500, but i sometimes see some sessions reaching more than 1000.
Each time, it's on session from a web server (we use 2, with 3 JAS on each), for the "JDBC Thin Client".

What i noticed was that these kind of sessions had many open cursors on a particular sql request on media objects (F00165).
It's really obvious !
For instance, the other i saw one "jdbc thin client" session reached 1277 open cursors.
For that session 1235 cursors were related to that sql request.

And anytime i look at open cursors, by sql request id, this particular request has between 5 to 10 times more opened cursor than any other request.
When one session reached 1277 open cursors, there was 5199 opened cursors for that sql id. The next sql id in top 10 of opened cursors had only 354 cursors.

So, this sql request is the following :
SELECT GDOBNM, GDTXKY, GDLNGP, GDTXPO, GDCRTU, GDDQE, GDTENT, GDMUSE, GDUPMJ, GDTDAY, GDEFTJ, GDEXDJ, GDPNTC, GDISTM, GDISFL,
GDTXTL, GDIMGL, GDOLEL, GDMSCL, GDFUTL, GDTXVC FROM PRODDTA.F00165 WHERE (GDOBNM = :1) AND (GDTXKY = :2) ORDER BY GDLNGP ASC

I suspected that it could be a request from standard JDE program, when you access standard media object, like from P31225 with work orders, etc. And furthermore
i know we developed some custom media objects accessed from P31225.
So i did some tests from my development platform and indeed, could confirm that this request was called each time you open some standard media object from standard
program, and also with our custom media object.
But looking at eventrules, i saw we were using the standard to access our custom media object, using some call to a system function "Media Object Structures".

So my questions :
- Did anyone already faced that kind of issue ?
- Is there any way to change the way to access media object in order to explicitly close cursors ? or something that could be done from eventrule ?
or anywhere else ...

the media object probably has the open cursor button selected on the form. remove the check mark, or have a developer remove the check mark from the media object on the form and this should resolve the issue.

Hi,

I don't see "open cursor" button anywhere.
Are you talking about properties of the media object control on the form ? or somewhere else ?
In "code generation option" there are 2 checkboxes called
"Default cursor on add mode" and "Default Cursor on update mode". But they are both unchecked and they are related to cursor position ...

Looked everywhere on the form properties or option, and on controls, or the system function to call media object, and couldn't find anything about cursor opening.