Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Rollback in Oracle

 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi in my application i am doing certain inserts into table which has certain unique constraints, when these unique constraints are voilated i want to roll back the whole bulk inserts,but my code does not rollback the transaction.

[code]
----------------------------------------------------------------------------

try
{
rs=stmt.executeQuery("Select MAX(CAST(CM_BATCH_SEQ as int)) from CM_FA_PLOT_STG_RUN");
while(rs.next())
{
batchseq=rs.getInt(1);
}
}
catch(Exception e)
{

String error=e.toString();

request.setAttribute("ProcedureError", error.replace("\n",""));
RequestDispatcher requestdispatcher=request.getRequestDispatcher("src/jsp/ProcedureError.jsp");
requestdispatcher.forward(request,response);
e.printStackTrace();
return;

}
int val= stmt.executeUpdate("INSERT INTO CM_FA_PLOT_STG_RUN (CM_BATCH_SEQ,RUN_DTTM,CM_RUN_STATUS,CM_BATCH_EXEC_STS)" +
"Select MAX(CAST(CM_BATCH_SEQ as int )+1 ),getdate(),'P','M' from CM_FA_PLOT_STG_RUN");
//System.out.println("val value"+val);
if(val==1)
{
int resultdata=0;
try
{
batchseq=batchseq+1;
for(int k=0;k<Integer.parseInt(count);k++)
{

int result=stmt.executeUpdate("INSERT INTO CM_FA_PLOT_STG_UP " +
"(CM_BATCH_SEQ,CM_ASPCL_CD,CM_PLOT_UP_STG_FLG)" +
"VALUES("+batchseq+",'"+plotno[k]+"','P')");
System.out.println("the result is"+result);
resultdata=resultdata+result;

}

}
catch(Exception e)
{
String error=e.toString();
request.setAttribute("ProcedureError", error.replace("\n",""));
RequestDispatcher requestdispatcher=request.getRequestDispatcher("src/jsp/ProcedureError.jsp");
requestdispatcher.forward(request,response);
e.printStackTrace();
return;

}

finally
{

if(resultdata==Integer.parseInt(count)){
System.out.println("when equal resultdata"+resultdata);
connection.commit();
}
if(resultdata!=Integer.parseInt(count)){
System.out.println("when not equal"+resultdata);
connection.rollback();
}


}


----------------------------------------------------------------------------

I have written the code to rollback in the finally block which definitly executes, the control even enters the block
if(resultdata!=Integer.parseInt(count)){}
but my transaction doesnot gets rollback please suggest me where and what am i doing wrong
 
carina caoor
Ranch Hand
Posts: 300
Eclipse IDE Oracle Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got it ... just need to setAutoCommit(false)
 
Campbell Ritchie
Sheriff
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are probably methods in the Connection or Statement which do the rolling back or auto-committing for you.
Have you written start transaction anywhere? That would probably be a better solution because it is atomic and will roll back automatically if there is an error.

There is something peculiar about a method with that many try-catches in. It just feels odd to me. It would be easier to read if you used the code button and maintained indentation.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic