Connection with Oracle database occur ORA-00942

[pyu]

In the Oracle database, I use SCOTT login, create a table and insert data for this table. In the ThingWorx composer, I made a connection thing to connect the TWX Composer with this database. When I tried to query this table with SQL(Query), there is an error as follow:

   "Unable to Invoke Service testSelect on xxx(xxx is the thing's name):ORA-00942"

I'm sure that the table which I selected in TWX is exist in the Oracle Database and I can select it by SQL Plus.

When I tired to query the other tables（such as BONUS, DEPT, EMP, SALGRADE） which are the default tables for the SCOTT account，the data in these tables can be queried.

But when I insert new data to SCOTT's default table, these new data can not be queried in TWX Composer as well. I'm sure these new data has been committed in the Oracle database.

How can I do?

Thank you

Yu

[supandey]

Hi Pengshan, the table you are querying from the ThingWorx Composer, does that exist in the Scott schema or outside the Scott's schema? Are you able to query this particular table using exactly the same username/password you are using to connect the Database thing?

Since if you can see the table there in the database then this error may point to the access rights for the user account.

[pyu]

Hi Sushant, the table that I'm querying from the ThingWorx Composer really exist in the Scott schema.  I can query this particular table using exactly the same username/password which is using to connect the Database thing.

How should I set the access rights in the Oracle Database then these tables can be accessed in the ThingWorx Composer?

Thank you for your help!

[supandey]

You can do following to check whether or not your access permissions is set correctly

1. Login with the same user ,with which you have setup the connection in ThingWorx Composer, SQL*Plus or SQL Developer

2. Check if you are able to perform CRUD on that table

3. If you are not able to perform CRUD operations on that table you'll need to grant this user enough access rights