How to insert more than one row in an Oracle table in one go

[Former Member]

Hi All,

I am working on a file-XI(BPM)-Oracle scenario.

I am sending an xml file to XI via a file adapter. On the receiver end i am using a jdbc adapter to insert the row in the database. There is a transformation step within the BPM which is converting the file to a format understood by the JDBC adapter.

The problem i am facing is that, even if my input xml file contains more than one recordset, only one row is created in the database with the first recordset.

How can i insert all the other recordsets in the database at the same time.

Pls guide

Thanks in advance.

Regards

Neetu

[Former Member · Accepted solution]

Hi,

As asked by Bhavesh, i set the occurence of the STATEMENT tab 1 to UB. But still only one row is getting updated in the database at a time.

As asked by Udo, i even tried the same by changing the occrence of ACCESS tab 1 to UB. But still same thing happened.

Can there be some other problem.

The transformation is being dane in the BPM.

As seen in SXMB_MONI, the input xml to the BPM has two RECORDSET tags but the output generated xml in the format understood by jdbc adapter has a single STATEMENT tag.

The two formats are given below

<b>Input:</b>

<?xml version="1.0" encoding="utf-8"?>

<ns:MT_Test_File xmlns:ns="http://namespace-EmpNumberGen">

<EmpGenRecord>

<APPLICANT_NO>20</APPLICANT_NO>

<FIRST_NAME>abc</FIRST_NAME>

<LAST_NAME>xyz</LAST_NAME>

<FUNCTION>ADMIN</FUNCTION>

<DESIGNATION>ZA</DESIGNATION>

<RESPONSIBILITY_LEVEL>dd</RESPONSIBILITY_LEVEL>

<RECRUITMENT_TYPE>dd</RECRUITMENT_TYPE>

<EMPLOYEE_TYPE>p</EMPLOYEE_TYPE>

<STATUS>A</STATUS>

<SEX>M</SEX>

</EmpGenRecord>

<EmpGenRecord>

<APPLICANT_NO>14</APPLICANT_NO>

<FIRST_NAME>abc</FIRST_NAME>

<LAST_NAME>xyz</LAST_NAME>

<FUNCTION>ADMIN</FUNCTION>

<DESIGNATION>ZA</DESIGNATION>

<RESPONSIBILITY_LEVEL>dd</RESPONSIBILITY_LEVEL>

<RECRUITMENT_TYPE>dd</RECRUITMENT_TYPE>

<EMPLOYEE_TYPE>p</EMPLOYEE_TYPE>

<STATUS>A</STATUS>

<SEX>M</SEX>

</EmpGenRecord>

</ns:MT_Test_File>

<b>Output</b>

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Emp_No_Generation xmlns:ns0="http://namespace-EmpNumberGen">

<Statement>

<SAP_EMPGEN action="INSERT">

<access>

<APPLICANT_NO>20</APPLICANT_NO>

<FIRST_NAME>abc</FIRST_NAME>

<LAST_NAME>xyz</LAST_NAME>

<FUNCTION>ADMIN</FUNCTION>

<DESIGNATION>ZA</DESIGNATION>

<RESPONSIBILITY_LEVEL>dd</RESPONSIBILITY_LEVEL>

<RECRUITMENT_TYPE>dd</RECRUITMENT_TYPE>

<EMPLOYEE_TYPE>p</EMPLOYEE_TYPE>

<STATUS>A</STATUS>

<SEX>M</SEX>

</access>

</SAP_EMPGEN>

</Statement>

</ns0:MT_Emp_No_Generation>

This is a bit urgent.

Pls help

Regards

Neetu

[bhavesh_kantilal · Accepted solution]

Hi Neetu,

Change occurence of STATEEMENT to 0 to UB.

next, in your message mapping , map

<i><EmpGenRecord></i> to <b>STATEMENT</b> in target.

Map, ACCESS to Constant.Check if the message mapping now is creating multiple STTATEMENT tags with all Records?

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Thanks alot.

The problem got solved.

I have given u full points.

Regards

Neetu

[udo_martens]

Hi Neetu,

you can have more statements, and, of course more rows in one statement. The example from SAP Library

  <StatementName2>
<dbTableName action=”INSERT”>
<table>realDbTableName</table>
<access>
<col1>val1</col1>
<col2>val2</col2>
</access>
<access>
<col1>val11</col1>
</access>
</dbTableName>  
  </StatementName2>

where you fill 2 rows, one with one field, one with 2 fields.

Regards,

Udo

[bhavesh_kantilal]

Neetu,

In the Datatype for JDBC , change the Occurence of the STATEMENT tag to 0 to UB.

Now for every record in the file to be inserted, one STATEMENT tag has to be created.

This way multiple insertions can be done.

<b><root>

<Statement> <i>---- 1 to Unbounded..!!</i>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

</dbTableName>

</Statement>

</root></b>

Regards,

bhavesh

[Former Member]

Hi ,

check this thread...

blog:

/people/alessandro.berta/blog/2005/10/04/save-time-with-generalized-jdbc-datatypes

sekhar