CODEWITHSUNDEEP
vb.netoracle-database
Nianios
Nianios

Reputation: 1436

Oracle transaction Rollback doesn't work

I created an Oracle transaction trying to save some data in two tables and I caused an error at the second procedure to call the rollback and check if it works.

The first procedure creates a job in USER_SCHEDULER_JOBS using the sys.dbms_scheduler.create_job.

Private Function CreateJobDef() As Integer
    Try
        Cursor.Current = Cursors.WaitCursor
        conn = New OracleConnection
        conn.ConnectionString = gApp.ConnectString
        conn.Open()
        Dim cmd As OracleCommand = conn.CreateCommand()
        Dim oraclTrans As OracleTransaction
        oraclTrans = conn.BeginTransaction()

        cmd.Transaction = oraclTrans

        Try
            cmd.CommandText = "TEST.CREATE_JOB_SCHEDULE"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Clear()
            OracleCommandBuilder.DeriveParameters(cmd)
            cmd.Parameters("in_job_name").Value = txtName.Text
            cmd.Parameters("in_schedule_name").Value = cboSchedule.SelectedValue
            cmd.Parameters("in_enabled").Value = If(chkEnabled.Checked, 1, 0)
            cmd.Parameters("in_comments").Value = txtComments.Text
            cmd.ExecuteNonQuery()
            'Everything is OK
            '-----------------------------------------------
            cmd.CommandText = "TEST.Update_Job_Def"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Clear()
            OracleCommandBuilder.DeriveParameters(cmd)
            cmd.Parameters("in_job_id").Value = "WQwqwq" 'I set this to a string to cause an error in the second procedure to check the rollback
            cmd.Parameters("in_job_name").Value = txtName.Text
            cmd.Parameters("in_job_type").Value = cboType.SelectedItem
            cmd.Parameters("in_report_nav_id").Value = cboReport.SelectedValue
            cmd.Parameters("in_mail_address_to").Value = txtMailTo.Text
            cmd.ExecuteNonQuery()

            oraclTrans.Commit()
        Catch ex As OracleException
            Msgbox(ex.ToString)
            oraclTrans.Rollback()
        Catch ex As Exception
               Msgbox(ex.ToString)
            oraclTrans.Rollback()
        Finally
            conn.Close()
            If conn IsNot Nothing Then conn.Dispose()
            If cmd IsNot Nothing Then cmd.Dispose()
        End Try
    Catch ex As OracleException
          Msgbox(ex.ToString)
    Catch ex As Exception
           Msgbox(ex.ToString)
    Finally
        Cursor.Current = Cursors.Default
    End Try
End Function

The problem is that the first procedure is create a new Job, although I have caused an error at the second procedure and the rollback is called. Any ideas?

Thanks

Upvotes: 2

Views: 915

Answers (1)

David Aldridge
David Aldridge

Reputation: 52386

There is an implicit commit in a DDL statement and in many Oracle procedure calls that create or modify objects such as a scheduled job. Therefore you cannot rollback a job creation procedure.

Upvotes: 2

Related Questions