Resolve "table or view does not exist" error from OracleStoredProc

Follow

Users may receive errors when attempting to use the OracleStoredProc execution method:

  • Table or view does not exist
  • Unable to retrieve procedure list

So, why does this happen?

This issue occurs when the user account does not have access to query the Stored Procedures list.

By default, JAMS will run the following Oracle query to retrieve the StoredProc list:

SELECT object_name FROM dba_objects WHERE object_type = 'PROCEDURE' ORDER BY object_name

When the user account does not have access to run that query, they will encounter the listed errors. Users can add a key to their local User.config file to resolve this issue.

NOTE: Allowing user access to the query would also resolve the issue. These instructions will only outline how to resolve the error directly through JAMS.


Resolve this issue:

To resolve this issue, add a Key to the User.config file, then restart the JAMS Client.

Add a Key to the User.config File:

  1. Open the User Config file on the machine with the JAMS Client where the error occurs. This file will be located at C:\Program Files\MVPSI\JAMS\Client\User.config by default.
    User_Config_File.png
  2. Add the key, OracleStoredProcQuery, directly before </appSettings> in the User.config file, then Save and Close the User.config file. The Oracle SELECT statement can be amended as required.
    <add key="OracleStoredProcQuery" value="SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE' ORDER BY object_name"/>
    User_Config_Edit.png

  3. Restart the JAMS Client.

 

NOTE: If organizations prefer to make changes to Oracle security, users must be given permissions to dba_objects.

Have more questions? Submit a request

Comments

  • Avatar
    Vardan Harutyunyan

    Thank you for this post
    One additional things need to be done is following:
    If you running the SP not under the user that belongs to the SCHEMA where the SP locating, you need to create a symlink in the schema that belongs to user under which name you running a SP from JAMS.
    For example:
    SP_A locating in SCHEMA_A
    User you using to connect and run SP is JAMS_ETL_USER.
    So, you need to give execute permission to that SP_A and create a symling in JAMS_ETL_USER schema pointing to SP_A in SCHEMA_A