'OCI_SUCCESS_WITH_INFO': unable to catch ORA-28002 error code

[steacktartar]

Hi, I'm trying to catch the following "exception". This is more of a warning as it should not prevent connection but i want it to be displayed anyway

[oracle@xxxxxxx~]$ sqlplus toto/toto@orcl_pdb

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Nov 19 21:27:35 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 0 days


Last Successful login time: Tue Nov 19 2019 21:27:30 +01:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

and i'm unable to figure out how to do it, or even if cx_Oracle can do that. This is my test code. Could you tell we where i'm wrong ?

>>> try:
...     connection = cx_Oracle.connect('toto/toto@xxxx')
...     cursor = connection.cursor()
... except cx_Oracle.Warning as w:
...     print (str(w))
...     print ("warning")
... except cx_Oracle.DatabaseError as e:
...     raise
... else:
...     print ("all good")
...     print (connection.version)
... finally:
...     connection.close()
...
all good
18.0.0.0.0

regards, Alain

[anthony-tuininga]

The documentation makes it clear that this "exception" is not used. SQL*Plus displays the "error" which is found from an OCI_SUCCESS_WITH_INFO response. ODPI-C and cx_Oracle do not expose this at all currently. Raising an exception would be unhelpful, I think. Providing the information in some fashion would be useful, but how to do so has not become obvious yet anyway! Thoughts?

[steacktartar]

i was not sure, i was thinking that it might be of no use for cx_Oracle but implemented anyway, thanks for clarifying this. Indeed i'm able to catch this with OCI_SUCCESS_WITH_INFO in php for example. Maybe raising an exception is too much, but still it's in the API so it's a bit confusing :/ Putting that info into a property maybe ?

[anthony-tuininga]

Yeah, it would be useful information, but where to put it is the question! We'll consider this further and I'll mark this as an enhancement request.

[agodfrind]

We are in June 2023 and nothing much has happened. About the question:

Providing the information in some fashion would be useful, but how to do so has not become obvious yet anyway! Thoughts?

A possible design solution is the way this is implemented in Java:

jshell> Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/graphdb", "hr", "hr");
con ==> oracle.jdbc.driver.T4CConnection@472163c0
jshell> System.out.println ("Warnings: " + con.unwrap(OracleConnection.class).getWarnings());
Warnings: java.sql.SQLWarning: ORA-28002: the password will expire within 7 days
jshell> con.close();

I.e an array of warnings is included inside the connection object.

[cjbj]

@agodfrind I do appreciate your frustration - and appreciate the design thoughts. This feature had always been high on my wishlist too. Of course, a lot else has been happening for the benefit of everyone.

[agodfrind]

There is actually a simple way for applications to detect the about-to-expire password, by checking the account status in the database explicitly:

def get_account_status (con):
    cur=con.cursor()
    status=cur.execute("select account_status from user_users").fetchone()
    return status      

then:

>>> import oracledb
>>> con = oracledb.connect('scott/tiger@localdb')
>>> print ("Account status: ",get_account_status(con))
Account status:  ('OPEN',)
>>> con = oracledb.connect('hr/hr@localdb')
>>> print ("Account status: ",get_account_status(con))
Account status:  ('EXPIRED(GRACE)',)
>>> 

Furthermore, the EXPIRY_DATE column has the planned expiration date, so that allows you to find how many days are left until the password expires for good.

[anthony-tuininga]

Support for this was added in oracledb 2.0.0.