0

I am inserting rows into the columns of oracle table that I created manually on the DB with the number of column names and order that in the XML file. The XML is considered as a config file,so the users can manage the columns and update them as per business needs. My java code reads the XML file and pulls the column names from XML and through Document(content mgmt technology)API.

I can extract the values of those columns and insert the rows into the DB table. I am fine till here, Since my query runs on the timer for three hour this query runs,due to this I am getting duplicate values into the DB table--as the same old query run and the old result are pushed into DB.

I need to avoid this, it would be great if you someone can help me with this.

Code:

String rObjectIdCheck = null;
pSelect = dbConn.prepareStatement("select r_object_id from financial_data");

ResultSet rsSelect = pSelect.executeQuery();

while(rsSelect.next()){

    rObjectIdCheck =(String)rsSelect.getObject("r_object_id");


    System.out.println("The OBJECTID from DB is:"+rObjectIdCheck); 
    System.out.println("The ObjectID from DCTM extract:"+rObjectID);

    if (rObjectIdCheck!=rObjectID) {

        pStmt = dbConn.prepareStatement("insert into financial_data ("+sbAttrName.toString()+")" + "values" +"("+sbAttrValue.toString()+")");

        pStmt.addBatch();
        pStmt.executeBatch(); 
    } 

}
sbAttrValue.delete(0, iSBValue);
sbAttrName.delete(0,iSBName);

}
FDinoff
  • 30,689
  • 5
  • 75
  • 96
aaradhya
  • 1
  • 3
  • you want to insert or replace. This has lots of answers on it. http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table – greedybuddha May 18 '13 at 00:34
  • I want to insert the query. – aaradhya May 18 '13 at 03:29
  • Iam getting the following error when using the following query not sure if Iam using the right query..plz help.......pStmt = dbConn.prepareStatement("MERGE INTO financial_data using dual ON("+rObjectID+")" + "WHEN MATCHED THEN UPDATE SET ("+sbAttrName.toString()+")" + "values"+"("+sbAttrValue.toString()+")"+ "WHEN NOT MATCHED THEN insert into financial_data("+sbAttrName.toString()+")" + "values" +"("+sbAttrValue.toString()+")"); pStmt.addBatch(); pStmt.executeBatch(); Error:java.sql.BatchUpdateException: ORA-00920: invalid relational operator – aaradhya May 20 '13 at 15:27
  • No Luck..Can someone please suggest on this issue, its urgent.Thanks in Advance. "MERGE INTO errors using dual ON("+rObjectID+")WHEN MATCHED THEN UPDATE SET ("+sbAttrName.toString()+")"+ "=" +"("+sbAttrValue.toString()+")WHEN NOT MATCHED THEN insert into ERRORS ("+sbAttrName.toString()+") values ("+sbAttrValue.toString()+")"); – aaradhya May 20 '13 at 17:02
  • You should really update your question with this, it's informative and hard to read in comment form – greedybuddha May 20 '13 at 17:10

1 Answers1

0

You have the incorrect MERGE syntax. It should look more like the following. You should check to make sure you put in the right variables.

String query = "MERGE INTO financial_data a using dual ON(a.r_object_id="+rObjectID+")" +
    "WHEN MATCHED THEN UPDATE SET "+sbAttrName.toString()+"="+sbAttrValue.toString()+ 
    "WHEN NOT MATCHED THEN insert ("+sbAttrName.toString()+")" +
    "values" +"("+sbAttrValue.toString()+")";
dbConn.prepareStatement(query);
greedybuddha
  • 7,488
  • 3
  • 36
  • 50
  • please note, Iam using only single table to insert. I get the error as ORA-00907: missing right parenthesis after firing the given query. – aaradhya May 20 '13 at 20:39
  • can you print out the query variable above and show me what it looks like. It doesnt look like its missing a parenthesis to me – greedybuddha May 20 '13 at 20:43
  • I copied and pasted the query that you provided: – aaradhya May 20 '13 at 20:53
  • yes but what does it say once you print it out? I can't see what you have stored in your variables – greedybuddha May 20 '13 at 20:53
  • pStmt = dbConn.prepareStatement("MERGE INTO ERRORS a using dual ON(a.r_object_id="+rObjectID+")" +"WHEN MATCHED THEN UPDATE SET "+sbAttrName.toString()+"="+sbAttrValue.toString()+ "WHEN NOT MATCHED THEN insert into ERRORS ("+sbAttrName.toString()+")" +"values" +"("+sbAttrValue.toString()+")"); pStmt.addBatch(); pStmt.executeBatch(); java.sql.BatchUpdateException: ORA-00907: missing right parenthesis at oralce.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:345) at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10844) – aaradhya May 20 '13 at 20:55
  • I mean what does it say **after** you substitute the variables. so no "+" signs, no "sbAttrValue.toString()", what is the actual query once those get replaced? – greedybuddha May 20 '13 at 21:06
  • The values are substituted at run time-- the columns names Iam pulling from XML where as the values of those columns will be generated by API. so it subsitutes those columns names and values.something like below...(object_name,r_object_id,r_creation_date,r_object_type,scanned_by,scanned_id,client,r_modify_date,scandate,batch_nbr,docset_id,form_id,form_type,i_folder_id)values('20161408.pdf','09006bfb800e2bc3','5/10/2013 2:50:27 PM','adp_ei9','ito-anydocidimport1','5 20130510 59580','119627','5/10/2013 5:52:14 PM','130510','00359648','20161407','idphoto2','PHOTOID','0b006bfb800156a4') – aaradhya May 20 '13 at 21:19
  • Well, it's not in the output you gave me. But you are definitely just missing a right parenthesis ")", find that and your problems are probably over – greedybuddha May 20 '13 at 21:23
  • not able to post the whole output due to space limitation...but I also considered another apporach.To fire a select query: Select r_object_id from ERRORS and then compare this r_object_id vs the r_object_id I get from API, if both are not equal then insert, however its taking tonnes of time at executeBatch() method.. – aaradhya May 20 '13 at 21:35
  • pSelect = dbConn.prepareStatement("Select r_object_id from ERRORS"); ResultSet rsSelect = pSelect.executeQuery(); String fromQuery = null; boolean flag = true; while(rsSelect.next()){ fromQuery = rsSelect.getString("r_object_id"); flag =fromQuery.equals(rObjectID); if (!flag) { pStmt = dbConn.prepareStatement("insert into ERRORS ("+sbAttrName.toString()+")" + "values" +"("+sbAttrValue.toString()+")"); pStmt.addBatch(); pStmt.executeBatch(); sbAttrValue.delete(0, iSBValue); sbAttrName.delete(0,iSBName); }}} – aaradhya May 20 '13 at 21:37