Receiver JDBC - Mutilple Records simultaneously

[Former Member]

Hi All,

My Scenario is Webservice -> PI -> Oracle DB (huge volume of data) .

I think by making use of stored procedure or SQL query we can insert one record at a time . Means , Stored procedure is called from PI for each record (since we map root node to statement node of target structure).

Please let me know whether my understanding of stored procedure executing per record is right or not (or it can process whole table or multiple records at once) ?

Thanks and Regards,

Midhu

[Former Member · Accepted solution]

Hi Midshu,

As per as my exp about this type of scenarios, Stored procedure inserts only one record at a time.

I think we have two options, But here i will give you one option.

option: This is simple Idea with follow the below instructions:

1) create a temporary data table as same like as your main table.

2) create the target message type with two types of statements structures, First Statement Structure is "INSERT" and as well as Stored Procedure structures

Note: Message type accepts multiple statement structures.

3) First in Insert structure statement you just assigned table name as a " temparory data table" and In Stored procedure structure you assigned table name as "main table"

note: INSERT structure statement insert the multiple records at a time.

process: As per as your message type , First data insert into the temparory table, after that immediatly go to call the stored procedure structure.

So in your stored procedure you write code for Insert the data from temparory table to main table.So your problem solved.

I hope it is very simple, but check with your customer to create the temparory table.

Note: for high volume of data insert into target side, This is good procedure, I allready implemented this procedure in my scenario.

Regards,

sateesh

Edited by: sateesh kumar .N on Oct 29, 2010 1:27 AM

[Former Member · Accepted solution]

Thanks Satheesh... will try your Idea and update you the results.

Regards,

Midhusha

[Former Member · Accepted solution]

Does Satish's Idea worked ?

Looking forword for ur approach and reply .

[Former Member]

Thanx sathish ..able to sort it out by making use of your Idea.

[Former Member]

Hi ,

Generally Speaking ... Stored Procedure has this functionality to pass multple Record at a time, with Cursor Type IN parameter .

TY_CUR is a Cursor Type in DB.

Like eg : Test_StoredProcedure(a IN NUMBER, B IN TY_CUR)

Through Receiver Adapter, frankely speaking Never Tried, call such type of stored procedures in Receiver Datatype/ receiver JDBC Adapater.

But Surely what you can do is , You can Send Data through Java UDF ... But Inserting/Updating Data in DB through UDF is not a Great practice.

Hope this help.

Regards

Prabhat Sharma.

[stefan_grube]

the SP is for one record only, but you can add many sp commenad in the payload, they will be executed one after the other.