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)


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.

Service Studio 11.8.9 (build 30789)
Integration Studio 11.6.29 (build 11480)

Rank: #1629

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.



Rank: #33715

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


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,

Rank: #1629

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

Rank: #1629

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.