Rollback in Oracle

[carina caoor]

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]

I got it ... just need to setAutoCommit(false)

[Campbell Ritchie]

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.