June 3, 2008 at 2:43 pm
If you use an Execute SQL Task to call an Oracle stored procedure,
and in that procedure you update a record, does the task itself auto-commit
that update in the database? I have found so far that appears to be
the case. I have no commit statements in the stored procedure, and the data is committed.
June 3, 2008 at 2:46 pm
I don't know about Oracle but in SQL Server, any INSERT/UPDATE/DELETE statements are automatically committed upon completion unless you have any logic that would direct it otherwise.
June 4, 2008 at 1:00 am
I would say it depends on what the setting is on the Oracle side.
Is it set up to auto commit or not?
~PD
June 4, 2008 at 7:03 am
The Oracle side is not auto-commit, but I think I know the answer for this.
I think a data flow is a transaction, and when it ends, it auto-commits,
even if when calling an Oracle stored proc to update a table that does not
do a commit itself. So far that is what is happening. I probably will add the commit
to the proc anyway to be safe. I suspect if I were to put a break point
on a task that is after the call to the stored proc, it will not have been committed
at that time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply