Transaction Commit or Rollback? 2

[cmmrfrds]

I need to insert/update a table daily and want this wrapped in a transaction that can be committed or rolled back at the end of the process. I have done this in Access and SQL Server. Can somebody give me the names/steps that I would use in Oracle. I am using a Merge Statement. I have the code for the Merge can somebody show me the rest of the code like so.

How do I begin the transaction code
Merge Statement
Decision to Commit or rollback code

Thank you.

 

[Dagon]

I think things are probably simpler in Oracle than you are used to. To begin a transaction, you just need to make sure anything there is nothing previously uncommitted in the session. So you could just do a rollback or commit at the start of your procedure.

A single merge statement will automatically be rolled back if there is an error, so you won't need to do much there. If you are looking at some more complicated processing where you run some tests, then you could code it like:

result := my_tests_on_data;
if result = TRUE then
commit;
else
rollback;
end;

 

[SantaMufasa]

cmmrfrds,

First of all, Oracle implicitly begins a new transaction after the following events:[ul][li]New connection to Oracle[/li][li]execution of a COMMIT or a ROLLBACK statement[/li][/ul]Additionally, an implicit COMMIT occurs following the successful completion of these commands:[ul][li]Data-Definition Language (DDL): CREATE, ALTER, DROP, OR TRUNCATE.[/li][li]Data-Control Language (DCL): GRANT or REVOKE.[/li][/ul]

So, one way to ensure that you have begun a new transaction is to explicitly issue a COMMIT (or a ROLLBACK) statement.

If you have executed a series of Data-Manipulation Language (DML) statements only (e.g. SELECT, INSERT, UPDATE, DELETE, MERGE), then, by definition, the changes have been neither COMMITted or ROLLedBACK. Once you have made all of the DML changes that constitute moving the business from one valid state to yet another valid state, then you should issue a COMMIT (or ROLLBACK) command.

To conditionally execute code (e.g. "Decision to Commit or rollback code"), we typically use PL/SQL to surround the conditional statements (and an accompanying COMMIT or ROLLBACK).

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

You can also use savepoints, which I haven't had much cause to use but are probably useful on occasion. The syntax goes:

savepoint x;
<do your work>
if result = TRUE then
commit;
else
rollback to x;
end;

The savepoint would only really be useful if there was some other uncommitted work (e.g. done by a previous procedure), which you didn't want to commit or rollback at this stage.

 

[SantaMufasa]

Sorry, I cross-posted with Dagon, but perhaps their is some value added in my post nonetheless.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

The DBA will be running the Merge Statement as a script, so from both the explanations my understanding is I don't need to issue any explicit statements since if there is a failure ALL the Merged records will be rollback. Correct?

 

[Dagon]

That is correct.

 

[Dagon]

Although obviously you will need to commit at some point if you want the changes to become permanent.

 

[SantaMufasa]

Yes, Oracle has what is called a statement ROLLBACK (different from a transaction ROLLBACK) in which if Oracle throws an error on a particular DML statement, then Oracle rollsback the changes that resulted from that STATEMENT only; Oracle does not rollback any un-committed changes that may have occurred prior to the erroring command.

For example, if you ran the following (excerpted) script:

SELECT...
UPDATE...
DELETE...
INSERT...
MERGE... <-- only error occurs here
SELECT...
UPDATE...
COMMIT;

...then by the time that Oracle executes the COMMIT statement, all of the changes both prior to and following the erroneous MERGE statement will COMMIT; the changes from the MERGE statement would have been rolled back, and thus not COMMITted.

Does this satisfactorily answer your question? Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

That answers it. Thank you very much.