Click here to Skip to main content
11,490,408 members (54,753 online)
Click here to Skip to main content

Tagged as

Gotcha: "BEGIN" in Oracle

, 23 Dec 2013 CPOL 10.8K 1
Rate this:
Please Sign up or sign in to vote.
"BEGIN" keyword in Oracle.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sam Varadarajan
Software Developer (Senior) City of Los Angeles
United States United States
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!
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150520.1 | Last Updated 23 Dec 2013
Article Copyright 2013 by Sam Varadarajan
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid