PL/SQL - Do I need to commit? 1

[MattWoberts]

Hi,

Very simple question, I hope

After a block of PL/SQL, we always do a commit. Is this needed:

BEGIN
...
..
END;
/
Commit;

Or does the "/" do the commit?

Thanks!

 

[SantaMufasa]

Matt

First, did we ever determine if you are related to the "Dwead Piwate Woberts?"

Secondly, the "/" following a PL/SQL block marks the physical end of the PL/SQL block and directs SQL*Plus to pass the code to the PL/SQL interpreter. It does not imply a "COMMIT". If your group are in the habit if issuing a "COMMIT" following a PL/SQL block, then that should be fine. Just understand these concepts:

1) "COMMIT" has an effect only if you have executed INSERT, UPDATE, DELETE, or a cursor in your PL/SQL block.
2) Syntactically, you may execute a COMMIT inside your PL/SQL or outside your PL/SQL block.
3) If you do not explicitly execute a COMMIT, then Oracle deals with any previously unCOMMITted changes as it would if those changes occurred as standard SQL: i.e., they remain uncommitted until you either:
a) issue an orderly disconnect (e.g. "exit", "quit") from SQL*Plus at which time Oracle automatically COMMITs your updates
b) suffer an abnormal end to your session (e.g., due to power failure) at which time Oracle automatically executes a ROLLBACK on your updates
c) issue an explicit COMMIT or ROLLBACK at which time Oracle executes what you requested.

And, of course, every DBA should be aware that
"COMMIT" happens ()...just a philosophical observation and a great line for a bumper sticker.

So, when your team, as a matter of habit, course, or standard, issues a COMMIT following the execution of a PL/SQL block, it is a choice, not a requirement.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:51 (29Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:51 (29Nov04) Mountain Time

 

[SantaMufasa]

...oops...and there is a "d)", as well..."

d) execute a SQL DDL ("CREATE", "ALTER", or "DROP") statement, at which time Oracle issues an implicit COMMIT to your previously unCOMMITted changes.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:55 (29Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:55 (29Nov04) Mountain Time

 

[MattWoberts]

Thanks a lot Mufasa - thats everything I wanted to know

PS.
I have no idea who "Dwead Piwate Woberts" is - maybe he's my long lost cousin.

 

[SantaMufasa]

Matt,

The "Dwead Piwate Woberts" ("Dread Pirate Roberts") is a central character/theme in the classic Rob Reiner movie, "The Princess Bride". Check it out at the video store and I guarantee you will not be disappointed.

Thanks for the star,

Dwead Piwate Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 15:28 (30Nov04) UTC (aka "GMT" and "Zulu"),
@ 08:28 (30Nov04) Mountain Time