Getting data from several tables (7) with JDBC sender adapter

[Former Member]

Hi Experts,

I have a requirement where i need to create Material Master records in SAP by getting data from 7 - 8 different Oracle tables (pretty much the same way we have different tables for different views in SAP).

There are validations that need to performed on this data and emails need to be sent out after each material is processed etc, therefore i want to get all this data into SAP through a receiver proxy.

But what I dont know is how to get data from 7 - 8 oracle tables in one JDBC sender comm channel, i have considered joins but there are so many tables and each table has 20 - 30 fields in them.

I know using stored procedure is another option but i have never worked with them, so i am not sure how they work... what needs to be the receiving interface structure for it? Should i create a flat structure with 140 - 160 fields?

Any help is appreciated.

Thanks,

Rudra

[Former Member · Accepted solution]

Hi Rudra,

Using Stored procedure will be good option in your scenario. Exactly Stored procedure is like a function call which you will call from XI and it will be executed at database level and return back the data which need to be processed by XI. Stored procedure will perform the entire task as per your scenario, it will select the values from all the tables and join then together and do all updation and other operations as per the business requirement. Then you just need to design the structure in Xi as similar way of the stored procedure.

So, either ask you db team to create sp as per the requirement and let you know about it.

Regards,

Nutan

[Former Member · Accepted solution]

Thanks, for all your responses... I will go with the stored procedure. I am working with the DB team to get a stored procedure which will fit the bill. Will let you know how it goes.

[madhusudana_reddy2]

Stored procedure will fit for your requirement. Develop Stored procedure in database system and call that from your sender JDBC Communication channel like below..

Query SQL Statement: Execute Yourprocedure name

Update SQL Statement: <test>

thanks,

madhu

[Former Member]

Hi

Using procedure in a sender channel is pretty simple. The procdure will return the results in row

Sender DT :

<DT_procd>

< row>

<param1>

<param2>

.

.

<paramn>

</row>

SQL statement : Execute <procedure_name>;

Regards

Ramg