Executing Stored Procedure with Signature on JDBC adapter

, ,
1

I am having issues when executing a Oracle stored procedure via the JDBC adapter.

I have configured a JDBC adapter to connect to our Oracle QA instance from my WM DEV instance. When I create the adapter service (in DEV) I can select current catalog, and see a list of schemas on this database. Choose the right procedure and everthing works fine within DEV environment.

When I promote this adapter service to my QA environment is when I start having problems. If I try to edit the same adapter service created in DEV I can no longer choose a schema name and it remains blank. When this executes it executes as “call ..” which of couse fails because it cannot find the procedure. When executed from DEV it executes as "call .. and runs without errors.

If I try to create a new adapter service on my QA environment using the same JDBC connector I receive an error about not being able to retrieve a list of schemas. Is this something I can correct within the JDBC connector or is this a Oracle issue?
I have tried using 2 different user accounts, schema account and another user granted access to procedure. I am using WM 8.2.

2

Hi There,

You better approach DBA and explain the problem to them. Let us see what they comment …

Thanks,

3

Share the error message when you create the adapter service.

Also make sure that your QA environment has the same stored procedure as that of development. And also compare the IS fix levels on dev and QA

4

Bruce,

In your QA can you try the JDBC Adapter service to have the same like in DEV and see if that works?

"call .. assuming schema and dbo path is same for DEV/QA destination oracle DB that you are trying to test SP.

You created manually the AS or deployed from DEV–>QA using deployer?

HTH,
RMG

5

Hi Bruce,

Are you sure the DB user have required access to stored procedure on QA env ? Try to access the QA stored proc using SQL developer with same user.

6

Problem has been solved and was within the database itself.

Our Oracle EBS instances are hosted on a 3rd party and the 3rd party DBAs considered it a security risk that the view ALL_USERS was granted to PUBLIC and had removed this grant.

This prevented webMethods, and other systems, from seeing schema/user names even if you were the schema owner.

This change was replicated into our QA environment because a refresh from Prod had just occured but they had not made he change within our DEV environments.

Thanks for everyone’s suggestions.

7

OK glad to hear issue resolved…and thanks for sharing the update! :smiley:

HTH,
RMG