How to delete data before insert in oracle database

[Former Member]

Hi friends,

I have one scenario.I want to send data from sap to Oracle database.Befor i am inserting data in database table,i want to clean/delete all the records in the table .This interface will run twice in a day.Can any body help me.

SAP-PI-JDBC

Thanks

Ravi

[Former Member · Accepted solution]

You can use :

1. A BPM with 2 send steps to db.

2. Stored Procedure (recommended): It can easily first delete all the records from the table and then instert all the records.

Refer the following threads on this topic;

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

Also, This requirement is dicussed many a times in the forum. Please run a search and I am sure you will get what exactly you are looking for.

[Former Member · Accepted solution]

Hi Puneet,

As per u r suggestions,i am using stored procedure.But i am getting following error.

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. MASTERXXXXXX' (structure 'Statement'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'NAME' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)'

I have define below structure.Please check the structure and guide me.

<Statement

Action attribute required

Table

Access

Empname string

Empid string

date date

Type attribute required

EXECUTE----

---

>Action

Table----

---

>MASTERXXXXXX

Type----

---

>char

Please check the above structure andguide me to changes if any requires.

Thanks

Ravi

[Former Member · Accepted solution]

Check your DATA TYPE attributes with the attributes of the column names in the Database table. There is a mismatch between the DT and Table in the database.

also, check the following:

/people/praveen.gujjeti/blog/2009/01/29/best-practices-with-receiver-jdbc-adapter-document-formats

Edited by: Puneet Singhal on Mar 21, 2011 7:06 PM

[Former Member · Accepted solution]

you can reach this requirement with out using BPM, stored procedure. Its very simple.

create 2 statements one for delete and one for insert.

EG :

<MT_Message>

<StatementDelete>

<TableClear>

<action> ....attribute mapped to SQL_DML

<access> ...element mapped to DELETE FROM Table

<StatementInsert>

<TableInsert>

<action> ....attribute mapped to INSERT constant

<table> ....element mapped to table name

<access> ...all the columns in my table

[rajasekhar_reddy14]

try Satish replay,that is good idea.

first do delete then do insert , if you want to maintain time gap(10 sec)like that then use one simple UDF.

Regards,

Raj

[Former Member]

Hi Ravi,

Take two statement structures in your target datatype.

One statement for Delete Structure and second statement for Insert Structure.

Please look into below link for JDBC structure format.

http://help.sap.com/saphelp_nw04/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm

Thank you ,

Sateesh