Ramandeep Singh

Published

Mon 13 February 2012

←Home

Optimized update insert ADF ?

// under ADF sql

I had a recently asked a question on bulk uploads in OTN forum. Seems the performance that i would receive for bulk update/insert scenarios isn't at par. So i created the procedure and a custom object type and table of that object type which provides far better performance. Let's say your table structure is as follows :-

1
2
3
4
5
--     TXN_TBL
("TXN_ID" Number,
"USER_NAME" VARCHAR2(50 BYTE),
"TXN_DATE" DATE,
"TXN_AMOUNT" NUMBER)

So you can basically create a object type mirroring that structure as follows .. code-block:: sql

create or replace type TXN_TBL_R is object ("TXN_ID" Number, "USER_NAME" VARCHAR2(50 BYTE), "TXN_DATE" DATE, "TXN_AMOUNT" NUMBER)

and then create a table type that will store record of these types:

1
create or replace type TXN_TBL_TB as table of  TXN_TBL_R

The procedure that will perform the updates and or inserts is shown in the below snippet:-

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
create or replace procedure B_INSERT(p_in IN TXN_TBL_TB) as
cursor for_insert is select * from TABLE(p_in) rt where not exists(select tmp.TXN_ID from TXN_TBL tmp where rt.TXN_ID=tmp.TXN_ID);
cursor for_update is select * from TABLE(p_in) rt where exists(select tmp.TXN_ID from TXN_TBL tmp where rt.TXN_ID=tmp.TXN_ID);
temp_insert TXN_TBL_R;
temp_update TXN_TBL_R;
begin
for temp_update in for_update loop
update TXN_TBL tmp set tmp.USER_NAME= temp_update.USER_NAME,tmp.TXN_DATE=temp_update.TXN_DATE,tmp.TXN_AMOUNT=temp_update.TXN_AMOUNT where tmp.TXN_ID= temp_update.TXN_ID;
end loop;
for temp_insert in for_insert loop
insert into TXN_TBL values(temp_insert.TXN_ID, temp_insert.USER_NAME, temp_insert.TXN_DATE, temp_insert.TXN_AMOUNT);
end loop;
end;

Then you can basically call this program from the ADF application using struct type. The snippet is shown below:-

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/**
*@param valueSet the set of bean values
*/
 public void someMethod(Set valueSet){
         Connection conn=null;
        try {
          conn = getDBTransaction().createStatement(1).getConnection();
          StructDescriptor tblRecordStructType =
                          StructDescriptor.createDescriptor("TXN_TBL_R", conn);
        Iterator it= valueSet.iterator();
        Object txnArray[]=new Object[set.size()];
          while(it.hasNext()){
       SomeCustomBean detail=it.next();
  STRUCT tempStruct=new STRUCT(tblRecordStructType,conn,new Object[]{detail.getTranxId(),detail.getUserName(),detail.getTxnDate(),detail.getTransAmount()});
    txnArray[i]=tempStruct;
    i=i+1;
    }
    //create array structure descriptor
     ArrayDescriptor txnTableDesc=ArrayDescriptor.createDescriptor("TXN_TBL",conn);
  //create an Array type with given structure definition
  ARRAY txnTableArray=new ARRAY(txnTableDesc,conn,txnArray);
           String callableProcedureStatement=" begin  TMP_INSERT(?); end;" ;
          OracleCallableStatement st=null;
          st=(OracleCallableStatement)getDBTransaction().createCallableStatement(callableProcedureStatement, 0);
          st.setARRAY(1, txnTableArray);
          st.executeUpdate();
    this.getDBtransaction().commit();
        } catch (JboException e) {
this.getDBtransaction().rollBack();
         throw new JboException(e.getMessage());

        }
  }

This is basically it. This will perform faster than normal ADF update/insert.

Go Top
comments powered by Disqus