Stored Procedure call from JDBC sender for Oracle Database

[Former Member]

Hi,

I have a requirement to call stored procedure using JDBC sender adapter for retriving data from an Oracle database.

I need to execute first stored procedure call from Querry SQL statement and then trigger second stored procedure call for confirming the succesful retrival of those records from the update SQL statement.

Querries:

1. Can we trigger stored procedure from Update statement of JDBC sender.

2. Can we call stored procedure from Querry statement, since I have read on other sdn threads that stored procedure calls on Oracle database are not allowed. If not possible to have "Execute stored procedure" would the function call (select * from table(function name)) work same as stored procedure.

3. In a JDBC sender the Querry statement and Update statement are executed in same session call and same database connection. What happens if the querry statement was not succesful, would the update be still triggered.

Please note PI does not have direct access to main table and hence the need to have separate stored procedure calls.

The PI version is PI 7.11 sp4.

Appreciate your inputs.

Thanks.

Siddhesh S.Tawate

[baskar_gopalakrishnan2]

>1. Can we trigger stored procedure from Update statement of JDBC sender.

I think not possible using update statement.

> Can we call stored procedure from Querry statement, since I have read on other sdn threads that stored procedure calls on Oracle database are not allowed. If not possible to have "Execute stored procedure" would the function call (select * from table(function name)) work same as stored procedure.

Yes using select query you can call stored procedure.

>. In a JDBC sender the Querry statement and Update statement are executed in same session call and same database connection. What happens if the querry statement was not succesful, would the update be still triggered.

No select and update handles in the same session using the same thread so if one transaction fails update should not proceed.

[Former Member]

Hi Baskar,

Thanks for the reply. One last querry

Can we trigger an Update statement on a table different than the one on which I have executed my Select statement(or stored procedure through it) ???

Thanks.

Siddhesh S.Tawate

[Former Member]

Yes you can execute an update statement on a table different from the one used in select, although it would be advisable not to for data consistency reasons.

[Former Member]

Thanks Shiladitya. The data consistency is being handled at Database end by maintaining timestamps from PI.