Hi all. Apologies for resurrecting this question, I thought I had discovered the answer but no luck.
We have an odd error occurring in an app built in Powerbuilder after an Oracle 19c upgrade. I am wondering if anyone else has seen this. Anytime the app tries to access the database, it is getting permissions issues. We have our permissions based around roles, in some cases there are few levels of roles applied (Role has "sub-role", which may have another "sub-role" and then the table grants etc. are applied at this lower level). This all worked absolutely fine in Oracle 12c. I am working in Powerbuilder 2019 R3 Build 2728. Here is a sanitised code snippet that throws the first error.
ls_SqlStatement= "SELECT B.<COLUMN_NAME>, " &
+ "A.<COLUMN_NAME>," &
+ "A.<COLUMN_NAME>," &
+ "A.<COLUMN_NAME>," &
+ "A.<COLUMN_NAME>," &
+ "A.<COLUMN_NAME>" &
+ " from " + lower(is_oraclesystem) + "<TABLE_NAME> A, " &
+ lower(is_oraclesystem) + "<TABLE_NAME> B " &
+ " WHERE ( B.<COLUMN_NAME> = A.<COLUMN_NAME> ) AND A.<COLUMN_NAME> = '<VALUE>' AND " &
+ "( ( A.<COLUMN_NAME> = ? ) )" ;
DECLARE select_user DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM :ls_SqlStatement using ato_trans ;
OPEN DYNAMIC select_user using :ll_usr_n ;
This results in an ORA-00942-Table or view does not exist error. The thing is, when you execute the SQL statement that the above resolves into in SQL Developer or TOAD, logged in as the same user as in the application, this SQL works fine. Also, if you introduce a syntax error within the Powerbuilder code like a missing parenthesis into the statement, Oracle catches it and the correct corresponding error is returned. So Oracle seems to be parsing it fine, it's only when it actually executes that it comes up with the issue. Prior to opening this dynamic cursor, the application does connect to the database and is able to run 1 simple select and alter session statements with no issue, so this is not the first database statement to run, this is just where the first failure is, but upon checking the 2 tables, the user should have all permissions via the roles I mentioned earlier.
This issue is only seen with this Powerbuilder application, no other application is having this error running in this database (we have no others in Powerbuilder either).
It could be a database bug or incorrect setting in Oracle also, I guess, but hoping for some advice, may be someone has seen this? I don't know Powerbuilder well, our expert left the company and this has landed in my lap.
Thanks for any help you can give.
