Detect Oracle Exception ORA-04068

4049070

Dear All,

I am connecting to an Oracle Database via Oracle.ManagedDataAccess.Core 2.19.31 (Database is Oracle 12c)

Pooling is enabled and when a package is recompiled on the DB we get ORA-04068 5 times (where 5 is the number of opened pool on the connection).

What I was able to accomplish is to execute the query in a try catch block and call OracleConnection.ResetPools().

That worked,since I was able to invalidate all the pools and get a smooth running on a new retry (wanted behavior). However, I am not satisfied with my solution because we will likely miss the try catch block in the code we will write in the future.

What I want to accomplish is to attach myself to something provided by the OracleConnection at a global level, detect the error and reset the connection automatically, so that developer won't have to care about it in this and in all the other projects we are developing.

OracleConnection.InfoMessage seems perfect, this is what the documentation reports: This event is triggered for any message or warning sent by the database.

However, this event is never triggered in my code, even when I get exceptions from the DB..

Am I loosing something? Do you have other solutions/idea?

BTW, keep in mind this:

Validate Connection attribute is not an option, since it is not efficient and our customer is really sensitive on that subject.

"Registering an Event Handler for Failover/TAF callbacks" is not an option either, since it is not supported by the managed dll.

Thank you in advance.

Best regards,

Mirko

Alex Keh-Oracle

I would recommend using a Try-Catch similar to something like the following:

==========

catch (OracleException ex) // catches only Oracle errors

{

    switch (ex.Number)

    {

        case 4068:

            // Reset Pool

            break;

        case 12545:

            MessageBox.Show("The database is unavailable.");

            break;

        default:

            MessageBox.Show("Database error: " + ex.Message.ToString());

            break;

    }

}

==========

I took this code from the following article: https://www.oracle.com/technetwork/articles/dotnet/vs2010-oracle-dev-410461.html

4049070

Hi Alex,

Thank you! With the try-catch block it works, but it forces all of our developers to remember this rule.. I would like to have a trigger at a global level (to be placed in our internal framework) which notifies me in case of unhandled exceptions so that I can place the logic you suggested me.

It was surprising to see that InfoMessage event handler is never triggered, from the description I though it was the perfect candidate for my solution..

Is there a different option in the Oracle.ManagedDataAccess.Core library to be notified in case of unhandled Oracle exceptions? Am I loosing something about InfoMessage event handler?
Thank you in advance.

Mirko

AndrewSayer

This error can’t just be ignored - it’s telling you that values that were previously set for you have been nullified. If you are in the middle of a process then you need to restart the entire thing as you’ve lost some of your variables.

If you don’t think it’s important for this then it’s possible that you dont really need global variables in your packages. Is that the case? The error will only be raised to sessions that have used a package with global variables that gets recompiled.

The proper fix for this problem is to use Edition Based Redefinition, this means that the version of the code that your session uses will always be the same until it manually asks for a different version. It’s very useful but some effort to set up https://docs.oracle.com/database/121/ADFNS/adfns_editions.htm#ADFNS020

Alternatively, you would do your code changes in an offline window (Which is very normal)

In a pooled session scenario, it’s a good idea to call dbms_session.reset_package https://docs.oracle.com/database/121/ARPLS/d_sessio.htm#ARPLS68060  at the end of each call so that the next call does not have access to the previous calls global variables (and also don’t have to hit this error if the packages are recompiled).

4049070

Thank you Andrew,
Very clear response!

Is there any implication (at a performance level) in using the dbms_session.reset_package?

If I got it right, that call is supposed to be included in the package I call as the latest instruction (db side), right? Or is it supposed to be a separate call from my backend towards the DB?

Also, a lot of article on the internet suggest to use Edition Based Redefinition. I have the feeling is an option which you can't use 100% of the time cause sometimes could be a problem to run code with a deprecated logic.. Is that true? I am not an expert, so I would like to know your opinion on it.

Thank you in advance.

Mirko

Alex Keh-Oracle

InfoMessage should work in ODP.NET Core. If it's not working, it's either a bug or the setup code is not correct.

4049070

Hi Alex,

Is there a place in which I can report this behavior/bug?

Thank you in advance

Alex Keh-Oracle

You can file the bug using My Oracle Support.https://support.oracle.com/epmos/faces/MosIndex.jspx 

If you don't have an account, your DBA should be able to file the bug on your behalf. Be sure to provide a reproducible test case.

You can also publish your test case here and I can file the bug on your behalf.