cancel
Showing results for 
Search instead for 
Did you mean: 

Combining DELETE and INSERT statements in JDBC receiver

Former Member
0 Kudos

Hi experts,

I am working on a scenario in which i have to post some data in a remote Oracle Database.

My DB team requested me to do a previous deletion of some records (with a condition) and afther that, to insert the new ones.

So i created a DT structure as:

DT_

STATEMENT_DELETE (1....Unbound)

TABLENAME

ACTION (required attribute)

TABLE

KEY

Condition 1

STATEMENT_INSERT (1....Unbound)

TABLENAME

ACTION (required attribute)

TABLE

ACCESS

Values

But i am concerned about one thing:

As i will be sending several rows in every jdbc access, i would like to perform the deletion of all rows at first, and then perform the insert.

So, basically, i would like to know how the sequence is executed as i dont want an infinite loop.

Could you please help me on this?

Best regards,

david

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

hi david

check the below blog

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,David:

BPM is not needed!I meet the same requirement as yours in my current project!

In my scenario,there is a table named "DC_IN_D_PRODUCE" in a Oracle DB.First,i need delete all rows in the table.Then i need insert some rows into that table!

I defined a data type in IR as follow:

Data type's name:ZDT_PP_01_DB_IN

ZDT_PP_01_DB_IN Complex Type

-DC_IN_D_PRODUCE Element 0..1

--dbTableName Element 1..1

---action Attribute required string

---table Element 1..1 string

---Access Element 0..unbounded

-


COUNT_TIME Element 1..1

-


ORG_CODE Element 1..1

In the message mapping:

You should duplicate a subtree of DC_IN_D_PRODUCE in the target message type as follow:

First substree to delete all rows in the DB table

"DELETE" --> action

"DC_IN_D_PRODUCE" --> table

Second substree to insert rows to the DB table

"INSERT --> action

"DC_IN_D_PRODUCE" --> table

Source node which is "0..unbounded"--> Access

Because you want to delete all the rows in target table,you have to deactive the "Key Tags Mandatory" attribute in JDBC Receiver CC.Otherwise,error will occur in RWB

former_member194786
Active Contributor
0 Kudos

Hi David,

Oops sorry got confused.

Can you tel us what is your sender? If it is a file what will be the format of the input file?

Regards,

Sanjeev.

Edited by: Sanjeev Shekhar Singh on Jul 11, 2008 2:12 PM

Former Member
0 Kudos

Hi,

By Using BPM first delete the records and then after than insert the records accordingly.

thanks

Kamath