Suggest Design Approach for data insertion in Oracle Database.

[Former Member]

Hi Experts,

In one of my requirement (Proxy to JDBC) i need to do the following.

1) the sender proxy has to insert large number (50K to 1 million) of records in to Database tables.

2) Before insertion it will check the LOCK/UNLOCK status of the tables. This status is in a separate single column table.

3) If UNLOCK then i want to make it LOCK and then delete the existing records from the table and then insert huge number of records.

4) then i want to again change the status to UNLOCK.

can you please guide the Best and efficient design approach.

Thanks

Ajay Garg

[VijayKonam · Accepted solution]

Hi,

Going with stored procedures would have been the best solution if it was SQL Server. Since Oracle does not support SPs, You might want to do a BPM scenario for this. You need to use mulitple receiver steps - one for locking then for insertion and then for unlocking. If you need to wait still you are able to lock, you might want to have a wait and loop step together in the bpm for getting the lock. You need to set a container variable based on lock. If it is not set you need to go in loop and get the lock (for getting the lock you need to write the query in one of the JDBC receiver adapters).

the BPM might look like -

Receive - [Loop- Send(Locking Step)-Container-Wait ] - Send (JDBC insertion) - [Send (JDBC unlock)] - End

VJ

[Former Member · Accepted solution]

Hi,

You can always use Stored Procedure with Oracle. That will the best way to do that. XI does supports invoking SP's on Oracle DB. Let me know if you have any more queries on invominh SP's from XI

Thanks

Amit

Reward points if answer is useful