Forum Stats

  • 3,769,230 Users
  • 2,252,933 Discussions
  • 7,874,951 Comments

Discussions

JDBC with Oracle Rollback stratergy

User_19BPU
User_19BPU Member Posts: 1,086 Blue Ribbon

Hi,

  I have multiple schema from where I am reading the records and writing the records in batches. I am using JDBC with Oracle 11g , please let me know how we can have a rollback if something goes wrong with any of the database transaction? Whether JDBC does have a support for the rollback or whether we use to use Oracle Savepoint to mark the transaction and indicate which part of the transaction needs to be rollbacked? Pleas clarify. Any links on this is highly appreciated.

Thanks.

Tagged:

Answers

  • Joe Weinstein-Oracle
    Joe Weinstein-Oracle Member Posts: 516
    edited Oct 13, 2015 9:38AM

    Look at the JDBC spec at the Connection methods, setAutoCommit(boolean), commit(), and rollback().

    This is something you can test yourself with a 15-line program.

  • Unknown
    edited Oct 13, 2015 3:25PM
      I have multiple schema from where I am reading the records and writing the records in batches. I am using JDBC with Oracle 11g , please let me know how we can have a rollback if something goes wrong with any of the database transaction? Whether JDBC does have a support for the rollback or whether we use to use Oracle Savepoint to mark the transaction and indicate which part of the transaction needs to be rollbacked? Pleas clarify. Any links on this is highly appreciated.

    Only YOU know what the requirements are for each of your transactions.

    A transaction begins with the first statement issued by a session and ends with a commit or rollback.

    If your use case requires that the ENTIRE transaction be either committed or rolled back then you do NOT need savepoints. Just issue a COMMIT if it completes successfully and a ROLLBACK if there is any exception.

    If you want to save incremental progress during your transaction then you need to explicitly use savepoints. The SQL Language doc shows the syntax and has example code

    https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10001.htm

    Use the SAVEPOINT statement to create a name for a system change number (SCN), to which you can later roll back.
    . . .
    Example 
    Creating Savepoints: Example To update the salary for Banda and Greene in the sample table hr.employees, check that the total department salary does not exceed 314,000, then reenter the salary for Greene:
    UPDATE employees SET salary = 7000 WHERE last_name = 'Banda'; SAVEPOINT banda_sal;  UPDATE employees SET salary = 12000 WHERE last_name = 'Greene'; SAVEPOINT greene_sal;  SELECT SUM(salary) FROM employees;  ROLLBACK TO SAVEPOINT banda_sal;  UPDATE employees SET salary = 11000 WHERE last_name = 'Greene';  COMMIT; 
This discussion has been closed.