Parameters to stored procedure from sender JDBC

[Former Member]

Hi,

I am using the JDBC sender adapter to call the Oracle stored procedure. Oracle version is 10.2. hence I m using function call using select.

My concern is, how to pass the input parameter to Oracle function from JDBC sender.

Regards,

Kavita

[bhavesh_kantilal · Accepted solution]

Kavita,

This has been described in this blog,

/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-adapter

Regards

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

I have already configured adapter for calling table function. My concern is, can I pass the input parameter to this function?

If not, I need to change the approach, and need to use receiver JDBC[as need to pass input parameter].

Can JDBC Receiver adapter support stored procedure for Oracle[10.2], if it returns values not the cursor. Or we need to have call to function table similar to sender adapter?

-Kavita

[bhavesh_kantilal · Accepted solution]

Kavita,

When you mean an Input parameter on the Sender JDBC adapter, doesi ti mply you want to pass some value dynamically? Like maybe the TimeStamp? If yes, as far as I know the answer is No. Sender JDBC adapters allow Statis Queires ( including Using oracle Table Functions).

Receiver JDBC adapters support Oracle SP's. I have not given it a try practically, but I have seen a POC that was able to trigger a Synchronous Call to a Stored Procedure oon Oracle DB and get the response back. There was nothing special ( no changes )apart from the existing means of calling a SP and we were able to get the response back using a Oracle SP.

The cursor issue no longer holds but as it was not me who worked on this demo, I am not sure myself as to how different this call was. From the inputs I got , it looked like the same call to a SP like on any other database. On the receiver side, no Table Functions were used.

Regards

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Thanks alot for your inputs. This helps alot.

-Kavita

[prateek · Accepted solution]

Hi Kavita,

<i>I am using the JDBC sender adapter to call the Oracle stored procedure</i>

<i>Unfortunately, the sender JDBC adapter does not support Oracle's stored procedure/function. Unlike stored procedures from other database vendors, Oracle returns a cursor, not a resultset. The sender JDBC adapter must send a resultset to XI.</i>

[Not sure about latest versions of Oracle]

<i>can I pass the input parameter to this function? </i>

In my opinion, No

<i>If not, I need to change the approach, and need to use receiver JDBC[as need to pass input parameter]. </i>

This could be possible but havent tried it

<i>Can JDBC Receiver adapter support stored procedure for Oracle[10.2]</i>

Yes

Regards,

Prateek