Leveraging Large Object Types 427
14
The second part of the procedure selects the row where text will be added, locks the row
for updating, assigns the starting position to the length of the contents + 1 (so no data is
overwritten), and calls the
WRITE
procedure. This transaction is then committed.
Analyzing the Contents of an Internal
LOB
In this section, you can analyze the contents of an internal
LOB
by working with the
functions
INSTR
and
SUBSTR
. Execute the code in Listing 14.8, and make sure that you
have entered
SET SERVEROUTPUT ON
at the SQL*Plus prompt so you can see output as
the program executes.
L
ISTING
14.8
Extracting and Matching Data Inside
CLOB
s
1: DECLARE
2: /* This PL/SQL block finds patterns in a CLOB. It also
3: extracts part of the data from a CLOB with SUBSTR */
4:

I
NPUT
continues
18 7982 ch14 11/30/99 1:09 PM Page 427
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
35: SELECT CLOB_LOCATOR into Source_LOB
36: FROM LOBS
37: WHERE LOB_INDEX = 6;
38: v_Buffer := DBMS_LOB.SUBSTR(Source_LOB,11,v_Starting_Location);
39: DBMS_OUTPUT.PUT_LINE(‘The substring extracted is: ‘ || v_Buffer);
40:
41: END;
42:
The first occurrence starts at position: 16
The second occurrence starts at position: 49
The substring extracted is: Oracle Data
The procedure begins by selecting the data from Row 5, and reading the locator
into the
Source_Lob
variable. Using the
INSTR
function, the pattern
‘Oracle’
,
assigned to the
v_Pattern
variable, is searched for the first occurrence, specified by the
v_Nth_Occurrence
variable. The Starting Location is defaulted to the first position in the
CLOB

1: DECLARE
2: /* This erases the data in Row 6, and trims the data in
3: row 5 to one occurrence of the book title. */
4:
5: Source_Lob CLOB;
6: Erase_Amount INTEGER;
7: Trim_Amount INTEGER;
8:
9: BEGIN
10: -- Erase the data completely in Row 6
11:
12: SELECT CLOB_LOCATOR into Source_LOB
13: FROM LOBS
14: WHERE LOB_INDEX = 6 FOR UPDATE; -- Locks Row for Update
15: Erase_Amount :=DBMS_LOB.GETLENGTH(Source_LOB);
428 Day 14
L
ISTING
14.8
continued
O
UTPUT
A
NALYSIS
I
NPUT
18 7982 ch14 11/30/99 1:09 PM Page 428
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Leveraging Large Object Types 429
14

CLOB
s you will alter.
•
Erase_Amount
holds the number of bytes to erase from Row 6.
•
Trim_Amount
stores the number of bytes that should remain in Row 5.
The procedure starts by reading the locator for the
CLOB
into the variable
Source_Lob
.
Erase_Amount
is assigned the value of the length of the data in Row 6 by using the
GETLENGTH
function. The
ERASE
procedure is called and passes the
CLOB
locator, the total
bytes to erase, and the starting position for erasing the data, which is hard-coded to the
value
1
in this example.
The second half of the block reduces the data in Row 5 by half. The locator for the
CLOB
in Row 5 is read into the variable
Source_Lob
. The

One of the biggest advantages of temporary
LOB
s is their ability to improve performance
over the usage of persistent
LOB
s. By default
LOB
s are persistent in nature unless other-
wise defined. This improved performance is gained because there are no redo records of
logging occurring when temporary
LOB
s are used. Likewise, you can explicitly remove a
temporary
LOB
,thereby freeing up additional memory and tablespace.
PL/SQL operates on temporary
LOB
s through locators in the same way as for persistent
LOB
s. Because temporary
LOB
s are never part of any table, you cannot use SQL Data
Manipulation Language (DML) to operate on them. You must manipulated them by using
the
DBMS_LOB
package as you would with persistent
LOB
s. Security is provided through
the
LOB

s currently valid for that session. This is a great
place to monitor for unwanted overhead of unnecessary temporary
LOB
s.
Creating Temporary
LOB
s
To create temporary
LOB
s, you use the procedure
CREATETEMPORARY
, which resides in the
DBMS_LOB
package. The Syntax for calling this procedure is as follows.
18 7982 ch14 11/30/99 1:09 PM Page 430
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Leveraging Large Object Types 431
14
DBMS_LOB.CREATETEMPORARY (lob_loc, cache, dur);
In this syntax the parameters are as follows:
•
lob_loc
is the location of the
LOB
.
•
cache
specifies whether the
LOB
should be read into the database buffer.

Summary
In this lesson you have learned how Oracle handles large objects, referred to as
LOB
s. The
two types of
LOB
s are internal and external
LOB
s. Internal
LOB
s can be persistent or tem-
porary. External
LOB
s, called
BFILE
s, are files accessible to the operating system, rather
than data stored in a table. Internal
LOB
s can also be binary, character, multicharacter, and
fixed width. These have full transactional support and can be committed or rolled back.
LOB
s can have a maximum size of 4GB, or the size of an unsigned
LONG
integer.
Q&A
Q What is the difference between an external and an internal
LOB
?
A Internal
LOB

Quiz
1. What are the two types of internal
LOB
s?
2. What is the maximum size of a
LOB
?
3. Can you write to external files?
4. When copying
LOB
s from one row to another, is a new locator copied?
Exercise
Create a temporary
LOB
that is of
BLOB
datatype, that will not be stored in the buffer, and
that will be limited to the current call.
432 Day 14
18 7982 ch14 11/30/99 1:09 PM Page 432
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
In Review
You have finished your second week of learning how to pro-
gram in PL/SQL. The week started with learning about SQL,
creating and using tables, and working with stored procedures
and packages. You know all about encapsulation and grouping
similar procedures and functions together in packages. You
have also learned how to plan for and react to certain runtime
errors that can arise in PL/SQL code. This includes how to
write exception-handling routines to handle internal and user-

At a Glance
At this point, you should have mastered the basics of Oracle’s
PL/SQL language, from functions to procedures to cursors.
With this knowledge, you can now master the packages sup-
plied by Oracle, which offer some additional advanced fea-
tures. Each chapter guides you through a package or concept
and demonstrates its topic through an actual example you can
try.
Where You Are Going
Day 15 covers advanced topics such as managing transactions
and locks. You will then continue with the topics of dynami-
cally creating SQL and writing to external files. Next you are
exposed to the Oracle-provided package
DBMS_JOB
. Later in
the week, you will see how sessions communicate using the
DBMS_PIPE
package and learn how to manage alerts by using
the
DBMS_ALERT
package. Toward the end of the week you
will learn about the Java engine, which is a new feature of
Oracle 8i. You’ll see how Java classes can be loaded into
the database, and you’ll learn how you can interface your
PL/SQL code to Java methods. Finally, the week ends with a
discussion on how to use Oracle8i’s Advanced Queuing fea-
tures.
This is your last week reading this book. It’s the week with
the toughest and most challenging topics, but they are also the
most interesting topics. So forge ahead, and good luck!

ing data concurrency and data consistency, the user of the server would experi-
ence inconsistent data reads, lost updates, and nonrepeatable reads. In today’s
transaction and locks lesson you will learn:
•Starting and stopping transactions
•Implementing two-phase commits
•Using savepoints
•Using locks
21 7982 ch15 11/30/99 1:07 PM Page 437
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Types of Transactions
A transaction is a logical unit of work that is composed of one or more Data
Manipulation Language (DML) or Data Definition Language (DDL) statements.
For every transaction in Oracle, two situations can occur. If the statements in a transac-
tion complete normally, then the effects of the transaction are made permanent in the
database. This is called committing the transactions. The other situation occurs when any
one of the statements is unable to complete for whatever reason. In this case, the effects
of the transaction are removed from the database and the transaction ends. This removal
of the effects of a transaction is called rolling back the transaction.
Oracle provides two general types of transactions: read-only and read-write
transactions. The read-only transaction specifies that the queried data and all
queries within the same transaction will not be affected by any other transactions that
take place in the database. In other words, any subsequent query can only read changes
committed prior to the beginning of the current transaction. The read-write transaction
guarantees that data returned by a query is consistent with respect to the time the query
began.
The read-only transaction enforces transaction-level read consistency. This type of trans-
action can contain only queries and cannot contain any DML statements. In this situa-
tion, only data committed prior to the start of the transaction is available to the query.
Thus, a query can be executed multiple times and return the same results each time.
The read-write transaction provides for statement-level read consistency. This type of

can be one of the following values:
•
READ ONLY
—Establishes transaction-level read consistency.
•
READ WRITE
—Establishes statement-level read consistency.
•
ISOLATION LEVEL
—Establishes how DML transactions are handled. You have two
options here:
SERIALIZABLE
and
READ COMMITTED
. The
SERIALIZABLE
options caus-
es any DML transaction to fail if it attempts to manipulate any data object that has
been modified and not committed. The
READ COMMITTED
causes the same DML
transaction to wait for the previous DML lock to disappear. This is the default
nature of Oracle.
•
USE ROLLBACK SEGMENT
—Defines the appropriate rollback segment to be used.
The read-only transaction is the default mode of all transactions. With this mode, you do
not have a rollback segment assigned. Additionally, you cannot perform an
INSERT
,a

the transaction. The
ROLLBACK
statement is discussed further in the next section,
“Canceling a Transaction.”
,
S
YNTAX
,
21 7982 ch15 11/30/99 1:07 PM Page 439
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Committing occurs when the user either explicitly or implicitly saves the transaction
changes to the database permanently. Until you perform a commit, the following princi-
ples characterize the state of your transaction:
• DML operations affect only the database buffer. Because the changes have only
affected the buffer, these changes can be backed out.
•A rollback segment buffer is created in the server.
• The owner of the transaction can view the effects of the transaction by using the
SELECT
statement.
•Other users of the database cannot see the effects of the transaction.
• The affected rows are locked and other users cannot change the data within the
affected rows.
After the commit is executed, the following occurs:
1. Locks held on the affected rows are released.
2. The transaction is marked as complete.
3. The internal transaction table of the server generates a system change number,
assigns this number to the transaction, and saves them both in the table.
You use the
COMMIT
statement to explicitly make permanent the changes from a transac-

Managing Transactions and Locks 441
15
Canceling a Transaction
Rolling back a transaction means undoing any change that the current transaction has
made. To execute a rollback of the entire transaction, you issue the
ROLLBACK
command.
The following example illustrates the use of the
ROLLBACK
command to undo the effects
of the
UPDATE
command:
UPDATE TABLE employee
(set pay_rate = pay_rate * 1.25
WHERE pay_type = ‘S’;
ROLLBACK;
Alternatively, you can roll back a portion of a transaction by using the
ROLLBACK TO
SAVEPOINT
command. Savepoints are discussed later in this lesson, in the section
“Creating Bookmarks with Savepoints.”
When you roll back an entire transaction, the following occurs:
1. All changes made by the current transaction are undone, using the corresponding
rollback segment.
2. All locks on the rows caused by the transaction are released.
3. The transaction is ended.
When you roll back a transaction to a savepoint, the following occurs:
• Only the SQL statements executed after the last savepoint are rolled back.
• The specified savepoint in the

in the transaction either commit or roll back the transaction, thus maintaining complete
data integrity of the global database.
All implicit DML operations performed by integrity constraints, remote procedure calls,
and triggers are protected by Oracle’s two-phase commit.
Creating Bookmarks with Savepoints
Asavepoint is like a bookmark in the transaction. You explicitly place this bookmark for
reference at a later time. Savepoints are used to break a large transaction up into smaller
pieces. This allows you to roll back your work to intermediate points in the transaction
rather than roll back the entire transaction. For example, if you are performing a large
number of updates and an error occurs, you only have to roll back to the last savepoint;
you would not need to reprocess every statement.
The following code creates the savepoint named
master_credit
:
SAVEPOINT master_credit
Savepoint names must be unique within a given transaction. If you create a second save-
point named the same as an earlier savepoint, the previous savepoint is erased.
The following is an example of rolling back a transaction to the
employee_1
savepoint:
INSERT INTO employee VALUES
(6,’Tom Brandon’,3,1000.00,’S’);
SAVEPOINT employee_1;
INSERT INTO employee VALUES
(7,’Catherine Ann’,2,2000.00,’S’);
ROLLBACK TO SAVEPOINT employee_1;
In this example, the insertion of the employee Catherine Ann is removed from
the transaction. At the point of the rollback to the savepoint, the insertion of Tom
is the pending data in the current transaction.
442 Day 15

may incur some unwanted overhead in the database for a short period of time.
Using Locking
The Oracle database uses locks to give the user temporary ownership and control of a
data object such as a table or row. Oracle automatically locks a row on behalf of a trans-
action to prevent other transactions from acquiring a lock on the same row. You don’t
want simultaneous row manipulations by two separate transactions. Data locks prevent
destructive interference of simultaneous conflicting DDL and DML statements. For
example, Oracle prevents a table from being dropped if there are uncommitted transac-
tions on that table. These data locks are automatically released when the transaction
completes by a commit or rollback.
Oracle generates what’s known as a read-consistent view of data when you query data
and while at the same time the data is being manipulated by another user. When a query
is active, the results of the query do not change, regardless of any update manipulations
that occur. If the query is reexecuted, the data returned reflects any updates executed. The
next two sections examine two types of data locking: table and row.
Locking Tables
DML operations can obtain data locks for specific rows and for specific tables. These
locks are used to protect the data in the table when the table is being accessed concur-
rently by multiple users.
I
NPUT
A
NALYSIS
21 7982 ch15 11/30/99 1:07 PM Page 443
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
A transaction acquires a table lock when a table is modified by the following DML state-
ments:
INSERT
,
UPDATE

lock does not prevent any manual locking or
exclusive read and writes on the same table.
•
share lock
—The
share lock
table lock allows for other transactions to only
query and lock specific rows. This lock prevents all updates, inserts, and deletes
from the same table.
•
share row exclusive
—This table lock is accomplished only through the lock
table with the
share row exclusive
parameter. This lock only permits queries and
selects for
UPDATE
statements.
•
exclusive
—This lock allows the transaction write access to a table. This lock
means that other transactions can only query the table.
Implicit data locking occurs automatically for all SQL statements, so users of the data-
base do not have to explicitly lock any rows. By default, Oracle locks resources at the
lowest level possible.
In a multiuser database, locks have two different levels:
•
exclusive
—This prohibits the sharing of the associated resource. The first transac-
tion that acquires the resource is the only transaction that can alter the resource

FROM employee
WHERE pay_type = ‘H’
FOR UPDATE;
These row locks stay in effect until the transaction is completed or rolled back.
The row lock is always exclusive, which prohibits other transactions from modi-
fying the same row. When the row lock is issued, a corresponding table lock is also
issued to prevent any conflicting DDL statements from taking effect.
Explicit Locks
A transaction explicitly acquires the specified table locks when a
LOCK TABLE
statement
is executed and overrides the default locking mechanisms. When a
LOCK TABLE
statement
is issued on a view, the underlying base tables are locked. The syntax for the
LOCK TABLE
statement is as follows.
LOCK TABLE table_name IN lock_mode MODE NOWAITE;
In this syntax the parameters are as follows:
•
table_name
is the name of the table you want to lock.
•
lock_mode
is the mode you want. See a full listing of the lock modes earlier in this
lesson, in the section called “Locking Tables.”
•
NOWAIT
is optional. If it is specified, then control is immediately returned to the
transaction if there is already a lock on the data object. If

21 7982 ch15 11/30/99 1:07 PM Page 445
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The
DBMS_LOCK
Package
Oracle supplies a package with the database to assist you in managing the database
locks. This Oracle-supplied package is named
DBMS_LOCK
. Through this package, you can
request a lock of a specific mode, give it a unique name recognizable in another proce-
dure in the same or another instance, change the lock mode, and release the lock.
The following functions are included in the
DBMS_LOCK
package:
ALLOCATE_UNIQUE
,
REQUEST
,
CONVERT
,
RELEASE
, and
SLEEP
. They are described in more detail in the follow-
ing sections.
The
ALLOCATE UNIQUE
Function
The
ALLOCATE_UNIQUE

returns the lock identifier for the unique identifier generated by the
procedure.
•
expiration secs
is the number of seconds to wait after the last
ALLOCATE_UNIQUE
has been performed on a given lock, before permitting that lock to be deleted from
the
DBMS_LOCK_ALLOCATED
table.
Exploring the
REQUEST
Function
The
REQUEST
function, as the name implies, requests a lock with a given mode. The syn-
tax for the
REQUEST
function is as follows.
446 Day 15
,
S
YNTAX
,
21 7982 ch15 11/30/99 1:07 PM Page 446
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.