Calling a stored prodedure from a JDBC adapter

, ,
1

Hi,
I am facing some problems in creating a configured operation on JDBC adapter which calls a PL/SQL stored procedure. The procedure is a
recursive procedure and expects a cursor as input along with other parameters. could anyone help me on this…

The procedure is as follows:
PROCEDURE PROC_JDE_ITMMET (ITMNUM IN NUMBER, ITMSTKT IN VARCHAR, ITMSRP6 IN VARCHAR, ITMMCU IN VARCHAR, C1 IN OUT ItemPackage.outputCursor)

Thanks,
Praveen

2

Praveen -
I am not as versed with the JDBC Adapter as I am with the “old” WmDB package. With WmDB, it was critical that you used a JDBC driver that was robust enough to perform the JDBC operations you needed. The most popular driver for example is the JDBC “thin” driver. The thin driver works great for basic JDBC requests but it’s not able to handle more complex operations such as the stored procedures we were calling. In our case, we needed to execute Oracle SPs that took multiple IN parameters and returned multiple OUT parameters. For this SP call, we needed to use the more robust JDBC “OCI” driver.

Again, all of the above discussion is pertinent to the WmDB package and I can’t correlate it to the JDBC Adapter. Though I would imagine that the “DataSource Class” one defines in a JDBC Adapter connection is loosely related to the “DB Driver” used to create the WmDB $dbAlias.

Hope that helps,
Rajesh

3

Hi Praveen,

If you are using the wM Version 6.X, please do the following.

Once you configured the JDBC Adapter, In developer under your package and appropriate folder, create new Adapter service after selecting your JDBC Adapter, and then it will prompt/show you the screens to pass the appropriate parameters.

Addition to this, please do refer for more detailed Information … WM_HOME/IntegrationServer/Packages/WmJDBCAdapter/doc/WmJDBCAdapterUserGuide.pdf.

Thanks

4

Hi Praveen,
We have tried calling the stored procedure with no inputs and 2 outputs and it seems to have worked for us. If you carefully set the input and output parameters in the respective tabs on the configured operations window you should be good to go. Look at the WmJDBCAdapterUserGuide.pdf which should be able to guide you more into you solution.

Regards,
Bala