ORA-00942: table or view does not exist using more than 1 database role
Application Type
Traditional Web, Service
Service Studio Version
11.8.9 (Build 30789)

Hi,

I'm encountering an issue that I cannot figure out what I'm doing wrong.

We have our data stored in an Oracle Database (v12.1) and we're exposing that data via extensions to our (web-)application.
The database connection is realized via the database user X.
The select, insert, update and delete privileges on our tables are granted to role A and role A is granted to user X.

Using Integration Studio we can import the entities and make them available to Service Studio.

This works very well and over the last years we experienced no problems with this.
Of course until now :)

In the same database we have a new schema containing a few new tables.
The select, insert, update and delete privileges on these tables are granted to role B and role B is also granted to user X.
So now user X (in the database) is able to access both sets of tables through roles A and B.

In Integration Studio I can see and select the new tables in the new schema.
After publishing the extension I can update the references in Service Studio and start using the entities.

And here is where stuff starts to go wrong:
When I create a new server action and want to query the new tables I get the error: "ORA-00942: table or view does not exist".
That is not correct; the table does exist, I can do all actions on the database with user X.
There is a public synonym present; that's also not the issue.
And I can obviously access it in Integration Studio.

Extra detail; if I revoke the privileges of the new tables from role B and grant them to role A it actually works!
However, from an architectural point of view I'd rather avoid that.

So, main question: Is it possible that exposing different parts of the database through more than one role causes a conflict in OutSystems?
Is there something that I'm overlooking?

Any help would be appreciated.
Thank you in advance.

Versions:
Service Studio 11.8.9 (build 30789)
Integration Studio 11.6.29 (build 11480)
Oracle: 12.1.0.2.0

Right, it's been a while since I had this issue and I can only come up with one explanation.
I've reverted back to having Role A and B granted separately to user X.

As expected, at first that did give me the same issues as before.
But after a while this error stopped appearing.

My conclusion is that it is an issue between having a connection pool and the way how Oracle handles roles.
In Oracle a newly granted role only works when you start a new connection.
With the connection pool the sessions are not stopped (regularly). So they will not register the new role until they all have been dropped and reconnected.

This would explain that role B granted individually to X would not be visible immediately but B granted through A would work as A would already be visible to X.

Being able to reset the connection pool could help prove this theory, but I am not able to do that.

For now, my issue is resolved and I would suggest for anyone else who encounters this specific situation to either reset the connection-pool or be patient until all connections are reset.

Hi Fluitsma,

Can you please create an advance query (e.g.

SELECT {entityname}.*
FROM {entityname}

) and execute it and verify what's the schema user being taken by the outsystems (Check executed SQL tab)  when the query is being executed. If outsystems is appending the other schema user then it will give the table or view does not exist error. 

You may raise a support ticket with outsystems for the same. Or try a workaround to test, create another extension and select tables only from the new schema and see it works.


Thanks

Zubair

Hi Zubair,

Thanks for your reply.

I applied your suggestion using an advanced query but I still got the same result.
The query looked like

SELECT  "SCHEMA"."TABLE1"."COLUMN", "SCHEMA"."TABLE"."COLUMN2" 
FROM  "SCHEMA"."TABLE"

I ran the script (just to be sure) in the database and it did retrieve the data.

Creating a new extension also did not do the trick.
(And would not be desirable as I do need to join them with tables from role A and OutSystems won't let me join from 2 different resources).

In addition I did find another situation:

Instead of granting role B to user X; I saw another role C granted to role A.
As a test I tried one of the tables of role C and that DID NOT give me errors.

So I revoked B from X and granted B to A.
The database still worked as expected but OutSystems still resulted in the ORA-00942.

But as role C did work it must be something I'm doing wrong in terms of defining roles.
I'm going to try a bit longer and then indeed raise a ticket.

With regards,
Rogier

Thanks for updates, let us know once you find the solution, if its something related to granting the roles.

Not sure if its going to work but can you try saving the database connection details again, enter the same username and password, test connection and save the connection details. Then publish the extension which is using this database connection and then publish the application using the extension. Let me know the result.

Right, it's been a while since I had this issue and I can only come up with one explanation.
I've reverted back to having Role A and B granted separately to user X.

As expected, at first that did give me the same issues as before.
But after a while this error stopped appearing.

My conclusion is that it is an issue between having a connection pool and the way how Oracle handles roles.
In Oracle a newly granted role only works when you start a new connection.
With the connection pool the sessions are not stopped (regularly). So they will not register the new role until they all have been dropped and reconnected.

This would explain that role B granted individually to X would not be visible immediately but B granted through A would work as A would already be visible to X.

Being able to reset the connection pool could help prove this theory, but I am not able to do that.

For now, my issue is resolved and I would suggest for anyone else who encounters this specific situation to either reset the connection-pool or be patient until all connections are reset.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.