If you are coming to Oracle from another database, you are in for some surprises. One of them is the key word BEGIN. They don’t mean the same in Oracle. Read on.
A TRANSACTION block
In SQL Standards they list Transaction control statements such as START TRANSACTION,
END TRANSACTION, and SAVE TRANSACTION. Different database vendors implement this as they see fit. For e.g., Informix always had a “BEGIN [WORK]” statement to mark the beginning of a transaction block. The block will end when an explicit
COMMIT or ROLLBACK is issued or SQL ends abnormally (in which case it’s rolled back).
So in non-ORACLE database (Informix for example), you may have:
BEGIN [WORK]
–(1)
INSERT….
UPDATE…
DELETE…
COMMIT [WORK];
– << commit marks the end of the current transaction>>
In Oracle, there is no BEGIN [WORK] equivalent. It’s just “understood” when the first executable statement makes some changes to the database. This includes DML and DDL statements.
So, the above statement block in Oracle will be written as:
– << implicit Transaction block begins>>
–(1)
INSERT….
UPDATE…
DELETE…
COMMIT [WORK];
– << commit marks the end of the current transaction>>
So, there you have it. In Oracle, an implicit transaction block is started when the first DML (here INSERT) statement is executed and it is not started with an explicit BEGIN or
START
keyword. On the other hand,
COMMIT or
ROLLBACK in Oracle does mark the end of a transaction. (There are situations where a transaction may be ended implicitly as well. DDL statements for e.g., participate in implicit transactions).
BEGIN…END in Oracle
But then, you have surely seen a BEGIN (and END) in Oracle? Yes, you are correct about that keyword being there, only it’s a statement in PL/SQL not plain SQL. PL/SQL is a separate programming language in Oracle, just like C or Java, only PL/SQL is specialized to handle SQL more efficiently.
This BEGIN statement in PL/SQL can be easily confused with BEGIN [WORK] in other databases, if you are new to Oracle. A
BEGIN..END block in PL/SQL is merely a syntactical collection of statements (SQL, non-SQL). They don’t relate to transactions. However, since a bunch of SQL statements inside the
BEGIN..END block mark a single PL/SQL statement, a COMMIT (or
ROLLBACK) after a PL/SQL block could commit (or rollback) entire list of SQL statements within the PL/SQL block, thus implying
BEGIN had to do with a new transaction. Not so! Suppose you had couple of PL/SQL blocks (BEGIN…END) before a
COMMIT is issued, statements
in both those blocks are committed. If you stopped at the end of first PL/SQL block, (marked by the first "/"), it won't be committed yet.
– <PLSQL BLock 1>
BEGIN
<SQL statements>
END;
/
– <PLSQL BLock 1>
BEGIN
<SQL Statements>
END;
/
COMMIT;
Here COMMIT applies to all the statements above (here PLSQL blocks 1 and 2), showing us that PL/SQL block does not correspond to a transaction block.
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!
Submit an article or tip
