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:
- 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.configby default.
- 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"/>
- Restart the JAMS Client.
NOTE: If organizations prefer to make changes to Oracle security, users must be given permissions to
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.
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