API Gateway - Oracle R12 JDBC Connection and Stored Procedure Query Errors

[Anon Anon]

Hi All.

 

Our finance has just moved to an Oracle 12 database.

We had some package containing Stored Procedures that were accessed by the API Gateway using the com.l7tech.jdbc.oracle.OracleDriver driver.

 

Now when we try and call the stored procedures with Parameters we get the message:

Query testing failed: Bad SQL Grammar:Function/Procedure does not exist or incorrect number of arguments for GET_GL_CODE; expected 0, got 1; query generated was EXEC GET_GL_CODE(1,,,,)

 

I have tried converting variables to strings, passed values for all parameters etc. It only works with 0 Parameters passed e.g. EXEC GET_GL_CODE() or EXEC GET_GL_CODE(,,,,,)

 

This was working fine on previous versions of Oracle.

 

I raised a case with CA Support. They told me that Oracle 12 was not on the list of supported systems so they could not help! This was rather concerning as I would have thought that if CA are fully committed to the product then Oracle 12 would certainly have been supported by now. We are only just going live with the CA Gateway.

 

Please can anybody help or advise if:

a) They have Oracle 12 and are calling Stored Procedures with Parameters from a Perform JDBC Query policy assertion.

b) Have any advise on getting these working again.

 

This is really urgent so any help and advice will be very greatly appreciated.

 

Many Thanks,

Paul

[Anon Anon]

Hi.

 

I managed to resolve this so this is just in case someone else experiences the same problem.

 

Our Oracle Users were set up differently.

 

We now need to specify the User in the Specify Schema Option. I was specifying User.Package but the Package name still needs to be in front of the Stored Procedure Name in the Query

 

e.g. EXEC PackageName.StoredProcedure (${param1},${param2})

and Tick Specify Schema and add the Schema User

 

Cheers,

Paul