Power to Build

Home » CodeProject » Gotcha: “BEGIN” in Oracle

Gotcha: “BEGIN” 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). See here.

So in non-ORACLE database (Informix for e.g) 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 INSERT statement is executed and it is not started with a BEGIN or START.

BEGIN…END in Oracle

But then, you have surely seen a BEGIN (and END) in Oracle? Yes, you are correct about that statement being there, only it’s a statement in PL/SQL not plain SQL. PL/SQL is a separate procedural programming language in Oracle, just like C or Java, only PL/SQL is specialized to handle SQL more efficiently1.

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 ran the below block in SQL*Plus. Let’s say you updated a table. Try SELECTing the record you just updated, in another session. You won’t see the change yet, because we have not committed the below statement. That also shows END doesn’t have anything to do with Transaction.

-- **PLSQL BLock 1**
BEGIN
-- **SQL statements**
END;
/

And as another example, if you had couple of PL/SQL block (BEGIN...END) before a COMMIT is issued, statements in both those blocks are committed.

-- **PLSQL BLock 1**
BEGIN
--**SQL statements**
END;
/

-- **PLSQL BLock 2**
BEGIN
--**SQL Statements**
END;
/

COMMIT;

Here COMMIT applies to all the statements PLSQL blocks 1 and 2, showing us that PL/SQL block does not correspond to a transaction block.

[1] I think, differentiating between SQL and PL/SQL is the biggest challenge a newcomer to Oracle faces. PL/SQL is a procedural language with control structures like if, loops etc. You can build stored programs like Oracle Procedures, Triggers, Functions and Packages only in PL/SQL. It’s tightly coupled with Oracle SQL Engine causing some confusion. I will post more on this later.


Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: