Why do I keep getting error "ORA-04061: existing state of has been invalidated" without an-CodePudding

Environment

The company I work for has written a solution such that we can send and queue API calls to outside the database by putting them in a table. I for one use this to get some info from Jira that is inserted in the database afterwards so we can put relevant data in tables and use it on the frontend. I have written the following object types that handle most of the behaviour of the api calls:

O_API
O_REQUEST_HANDLER
O_RESPONSE_HANDLER

Basically the O_API object type takes in an O_REQUEST_HANDLER and an O_RESPONSE_HANDLER as arguments. Consequently methods are called from O_REQUEST_HANDLER to prepare and send the request. Then the response gets returned and this is then handled by O_RESPONSE_HANDLER.

I Created these object types since we have many different API calls to fulfill and the behaviour for each of these differs in preparing the request and handling the response, with this method I can create subtypes of O_REQUEST_HANDLER and O_RESPONSE_HANDLER and still pass them to O_API which calls the needed methods through the magic that is polymorphism. (There probably is a better suited design pattern for this but I have only just learned the strategy pattern since I'm still a student and an intern at the company I mentioned above). an example of how this is used in one of the procedures:

-- creating the request and response handler
l_request_handler := o_jira_get_issues_request_handler(l_startAt, l_lastupdated, l_boardId, l_maxresults);
l_response_handler := o_jira_get_issues_response_handler(l_lastupdated, l_boardId, l_getpagesconcurrently);
        
-- creating the api object
l_jira_api := o_api('Get Jira Issues (& Comments) API', l_request_handler, l_response_handler);

-- calling the procedure to handle the request
l_jira_api.p_processRequest;

All of the procedures which call on the api type are defined in a single package that only has local variables, no package-wide variables are defined.

The Problem

The thing is that once the API calls start executing from the queue sometimes errors are returned saying that there are invalid objects (the queue is basically a table that refers to the procedure which assembles and calls the associated api call).:

ORA-04061: existing state of  has been invalidated
ORA-06512: at SCHEMA.PKG_JIRA, line 112
ORA-06512: at line 1
ORA-06512: at SCHEMA.PKG_QUEUE, line 309

the line at which the supposed error is thrown is at the moment that the O_REQUEST_HANDLER subtype is created (in this case this line, other api calls also throw the same error):

l_request_handler := o_jira_get_issues_request_handler(l_startAt, l_lastupdated, l_boardId, l_maxresults);

This only happened after I added my latest API call so I'm perplexed as to why this happens, I should mention that this also doesn't happen with every API call (even when they call the exact same procedure to invoke the api call with the same request and response handler types), it seems to happen at random, the error is thrown more than it isn't thrown so the few api calls that do not throw errors now are the exception (pun unintended).

I also have to mention that when I invoke the procedure that handles the API call manually it never throws an error, it only does so when it is invoked by the job that runs over all the different entries in the queue.

What I've Tried (and didn't work)

I recompiled all my object types and object types bodies (which is kind of a pain I noticed if they have dependencies).
I also checked with a query to all_objects table if there were still invalid object types, which there aren't
removed the implementation for my api call after which the error began showing up.
request_handler has another object attribute, request_t, which holds all the request info. I also recompiled this manually to see if it would solve anything.
Manually invoked the procedures that create, send and handle the api calls, as I said before this works fine but it only throws an error when the queue of calls is ran by an external job.
Googling the error for solutions, followed instructions but they won't help unfortunately.

I hope this is enough information that anybody gets an idea about what the problem might be, I know I haven't shown a lot of code but the company prohibits me from doing so unfortunately, Thanks for reading and I will be looking forward to your answers!

CodePudding user response：

The error doesn't mean there are any invalid objects, so your queries shouldn't show any.

This is caused by a stateful package:

If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

You said that "no package-wide variables are defined" but the error suggests there must be something making it stateful.

Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state.

You are seeing the error after you recompiled the package, initially to add your new API (procedure) to it. The behaviour you saw implies your queue process had existing database sessions - possibly several, from a connection pool - and each of those that had previously referenced the package had state.

The package state persists for the life of a session, except in these situations:

The package is SERIALLY_REUSABLE.

The package body is recompiled.
If the body of an instantiated, stateful package is recompiled (either explicitly, with the "ALTER PACKAGE Statement", or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.
After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost.

That only refers to ORA-04068 but ORA-04061 is in the same class of issues.

The reason I think you probably have a connection pool is that the error didn't always happen. The first call you made after adding your new procedure would have errored, but the session that used would then have new state and wouldn't error again if you happened to reuse that; but if your queue picked a different connection from the pool then its session would then error the first time but then be OK, and so on.

The things you tried just extended the problem - recompiling everything, or just that package to remove the new procedure again, would have discarded the state on the sessions that had already encountered and cleared the error, so they would then hit it again.

Eventually all of the sessions would have hit the error and then had clean state, but that could have taken a while (depending on your pool size and how long it took to cycle through all of the connections); but your attempts to fix it didn't give it a chance.

Just encountering the errors and pushing through them isn't ideal of course. You haven't said how your queue operates but if you're using middleware (like WebLogic) there is usually a way to reset a connection pool, which closes all open connections and starts new ones - with fresh state.

If you know you have stateful packages then performing a reset, or if that's not possible then perhaps restarting the application/queue, may need to be part of the process you use when deploying changes to the package.

When you manually invoked the package that will have been in a new session outside the connection pool, which would have had its own fresh state - so that didn't error, as there was no pre-compilation state to discard.

If you can it might be worth reviewing all of the packages and other objects looking for what is making them stateful, and seeing if that is really necessary.