Powerbuilder 12.5 Oracle error 28002 during execute immediate

[Former Member]

I'm using Powerbuilder v 12.5 with an Oracle database.  I discovered a problem after upgrading Oracle database from 11.2.0.3 to 11.2.0.4.  I wrote a small Powerbuilder application to demonstrate this.  The application logs in as a user whose expiry_date is in the past, but whose account status is "OPEN" based on a query against the data dictionary view DBA_USERS.  After logging in the application performs an ALTER USER <user> IDENTIFIED BY <new password> REPLACE <old password>.  It does this via EXECUTE IMMEDIATE <stmt> USING SQLCA.  After this statement completes, the user's password is successfully changed in Oracle but the SQLCA object returns a -1 in its SQLCODE member.  The SQLCA.SQLDBCODE is set to 28002 and the SQLCA.SQLERRTEXT has the message "ORA-28002: the password will expire within 10 days".

When I run the same application using Oracle 11.2.0.3 and the same user in the same state, the SQLCA.SQLCODE is 0.  This behavior is obviously different from 11.2.0.4.

The same issue occurs with Powerbuilder 11.5.

We are using the SQLCA.DBMS="010 Oracle10g (10.1.0)".

I also tested it with the newer SQLCA.DBMS="ORA Oracle11g" for Powerbuilder 12.5.  This string doesn't work in Powerbuilder 11.5.

Oracle claims that there are no differences between 11.2.0.3 and 11.2.0.4. 

I'm wondering if Powerbuilder does an internal Oracle version check during EXECUTE IMMEDIATE ... USING SQLCA.

Sure we can code around it but that means rebuilding and testing our applications and redelivering them.  But, we would like to avoid that if possible.

If there is something simple we can do to mitigate this behavior such as using a different DBMS string or changing the Oracle DLL that would help us out tremendously.

Thanks and best regards,

Will

[former_member190719]

Oracle is what is returning the error, it's not PB making it up.

It sounds like you have two different password policies in effect on the different databases.

[Former Member]

Thanks for getting back to me quickly.

I did a comparison on both versions of the database.  As far as password policies go I queried the DBA_PROFILE dictionary view and compared the results between 11.2.0.3 and 11.2.0.4 and they are identical.

I switch between the Oracle versions by shutting it down.  Then I copy over the data files, redo log files, control files and temp files that constitute the other version.  Then I start that database up.

I did another query involving DBA_OBJECTS, DBA_PROFILES, DBA_SYS_PRIVS, DBA_TAB_PRIVS,and DBA_USERS.  This showed some differences but the differences are in the various data dictionary views that were changed between 11.2.0.3 and 11.2.0.4.

I attached the list.sql which does these queries and formats the results.

I attached two files contest3*.txt that are the results of these queries.

The only suppositions I can come up with are that the oracle interface DLL is checking the specific version of Oracle or its checking one of the data dictionary views.  Since I don't have the source code for Powerbuilder I don't have a way to see it.

Best regards,

Will

contest3_11.2.0.3.txt.zip

contest3_11.2.0.4.txt.zip

list.sql.zip