SP Call Issue

, ,
1

Hello,

I have a weird situtation where in the service end up returning null results when calling a SP thats supposed to return some values. I am using wM 6.1, Oracle 10g and JDBC Adapter.

I was calling a SP which returns the cursor as output. When I was calling this SP for the first time, it was returning the cursor with all the values from the desired table. But when I was calling the same procedure for the second time or further, the cursor wasnt returning any data. Using Local_Transaction and oci driver type in the configuration settings and does these settings have anything to do with that? anyone come across a situation like this??

appreciate your responses.

2

Any takers on this one plz???

3

John,

I haven’t invoked Oracle SP’s that return cursors using WM JDBC Adapter Services.

What is the behavior when you use the thin JDBC adapter rather than OCI? What happens with No_Transaction rather than Local_transaction?

Mark

4

Mark,

Thanks for the response. I tried using thin Driver as well as No_Transaction but the behavior is same regardless of Driver type and Transaction kind. Any more thoughts???

John

5

Is this the case with any stored procedure you have tried to call using jdbc adapter or only this one, try to narrow down your problem by writing a stored procedure that returns cursors, a simple one to start with and see how your jdbc adapter respond. If it is behaving normally then only above mentioned stored procedure is the culprit; In that case provide more info on your stored proc and we might be able to help.

6

BTW, have you applied all relevant updates and patches for the JDBC Adapter v6.03?

7

Adnan n Mark,

Thanks alot for your valuable suggestions. I will try to work taking your inputs and will let you know if the problem still persists.

Appreciate your responses.
John

8

Hi John - Note that in general, the OCI driver is more robust than the thin driver. For example, the OCI driver has to be used to return complex data types (e.g., arrays) whereas the thin driver only supports simple data types (e.g., integer, varchars, etc.).

9

Rajesh,

Thanks. Oracle cursor in our case returns a set of records that contain simple data types like number or varchars. Do you think thats a better idea to go for OCI driver since the SP call involves Cursor? (I will try to work with both driver types thou)
Also, what difference does that make using NO_TRANSACTION or LOCAL_TRANSACTION type?

10

john jdbc adapter user guide located in …\IntegrationServer\packages\WmJDBCAdapter\doc explains in detail what each type means, pls take a look at pg 15.

11

“Also, what difference does that make using NO_TRANSACTION or LOCAL_TRANSACTION type?”

For the connection transaction type you use, it will depend on whether or not you want to maintain a transactional context for the rest of your service. As Adnan mentioned, you can read up on the implementation details in the JDBC Adapter documentation.

12

Thank you guys. Appreciate your time and suggestions. At last i was able to make it work. I made changes to the Transaction types, driver types and also modified the SP.

I figured out the solution and my understanding is that since wM Adapter service doesnt have an option to close the cursor (Java apps do this thou) after fetching the data from the table, made changes to the SP logic (in DB) to see if the cursor is open and if its open, force it to close.

anyways, thanks again for your support guys.