Calling Stored Procedure from Oracle DataBase using Sender JDBC (JDBC-JMS)

[vuyyurujyothi]

Hi All,

We have requirement to move the data from Database to Queue (Interface Flow: JDBC -> JMS).

Database is Oracle.

*Based on Event, data will be triggered into two tables: XX & YY. This event occurs twice daily.

Take one field: 'aa' in XX and compare it with the field: 'pp' in YY.

If both are equal, then

if the field: 'qq' in YY table equals to "Add" then take the data from the view table: 'Add_View'.

else if the field: 'qq' in YY table equals to "Modify" then take the data from the view table: 'Modify_View'.

Finally, We need to archive the selected data from the respective view table.*

From each table, data will come differently, means with different field names.

I thought of call Stored Procedure from Sender JDBC Adapter for the above requirement.

But I heard that, we cannot call stored procedure in Oracle through Sender JDBC as it returns Cursor instead of ResultSet.

Is there any way other than Stored Procedure?

How to handle Data Types as data is coming from two different tables?

Can we create one data type for two tables?

Is BPM required for this to collect data from two different tables?

Can somebody guide me on how to handle this?

Waiting eagerly for help which will be rewarded.

Thanks and Regards,

Jyothirmayi.

[baskar_gopalakrishnan2]

>Is there any way other than Stored Procedure?

Can you try configuring sender adapter to poll the data in intervals. You can configure Automatic TIme planning (ATP) in the sender jdbc channel.

>How to handle Data Types as data is coming from two different tables?

If you use join query in the select statement field of the channel then whatever you need select fields will be returned. This might be fields of two tables. your datatype fields are combination of two diff table.

>Can we create one data type for two tables?

YEs. Answered above.

>Is BPM required for this to collect data from two different tables?

Not required.

[vuyyurujyothi]

Hi Gopal,

Thank you for your reply.

>Is there any way other than Stored Procedure?

Can you try configuring sender adapter to poll the data in intervals. You can configure Automatic TIme planning (ATP) in the sender jdbc channel.

-

---

I need to select the data from different tables based on some conditions. Let me simplify that.

Suppose Table1 contains 'n' no of rows. For each row, I need to test two conditions where only one condition will be satisfied. If 1st condition is satisfied, then data needs to be taken from Table2 else data needs to be taken from Table3.

How can we meet this by configuring sender adapter with ATP?

================================================================================================

>How to handle Data Types as data is coming from two different tables?

If you use join query in the select statement field of the channel then whatever you need select fields will be returned. This might be fields of two tables. your datatype fields are combination of two diff table.

-

---

we need to take data only from one table at a time. It is not join of two tables.

================================================================================================

Thanks,

Jyothirmayi.

[baskar_gopalakrishnan2]

>I need to select the data from different tables based on some conditions. Let me simplify that.

>Suppose Table1 contains 'n' no of rows. For each row, I need to test two conditions where only one condition will be satisfied. If 1st >condition is satisfied, then data needs to be taken from Table2 else data needs to be taken from Table3.

If you have such business requirement then you might need to go for stored procedure. Please refer in sdn forum for the automatic time planning scheduling details.,