Database Reference
In-Depth Information
Generally, in Oracle database, a
COMMIT
must be issued as needed, which is sometime after every
data update (
INSERT
,
UPDATE
or
DELETE
). Oracle
procedures
that update data should not issue a
COMMIT
-
they assume that they are just part of a transaction and that whatever code called them will issue a
COMMIT
when all the parts are successful. Tom Kyte, author of
Expert Oracle Database Architecture: Oracle
Database 9i, 10g, and 11g Programming Techniques and Solutions
(Apress, 2010) explains on his blog
(
asktom.oracle.com)
that procedures do
atomic
units of work, and all transactions must be controlled
(and
COMMIT
executed) by the invoker (the one who called the procedure).
Auto-commit is a mode of operation that you can employ where every update is immediately
committed to the database. You can turn this feature on by executing:
SET AUTOCOMIT ON;
You might think that this would defeat the ability to do multiple-step transactional updates, and you
would be right. However, this is the default mode for JDBC. So from a Java client, when you update the
database, that update is immediately committed, and you cannot roll it back. One benefit is that you
don't have to execute another Oracle statement using JDBC to
COMMIT
the update.
Actually you can turn off the auto-commit mode in JDBC by calling the
setAutoCommit()
method of
your
Connection
, like this:
conn.setAutoCommit(false);
Then when appropriate, you can commit or rollback your transaction manually with one of these
commands:
conn.commit();
conn.rollback();
Auto-commit is one area where pure Java is not “run anywhere.” For Java code running in the
Oracle database (Java stored procedures), the default mode is for auto-commit to be turned off. This
adheres to the proposition that procedures are atomic and that transactions are committed by the
invoker, but it is the opposite of what you'd expect from JDBC.
This is something to keep in mind as we develop Java stored procedures. If we update data, we need
to issue a
COMMIT
.
Chapter Review
This has been a rather brief chapter in which we've been able to focus on running Java in the Oracle
database, or Java stored procedures. We saw a couple techniques of loading Java into Oracle.
It may be off that subject, but we also discussed in some detail the reverse process of calling Oracle
database from Java using the
Connection
and
Statement
classes. We also diverged into a discussion of
FROM DUAL
and the
SELECT
query.
As we must do as we proceed, we stepped back to talk about general programming issues. In this
chapter, we covered some additional aspects of exception handling, and we covered declaring and
initializing members.
Most important, we discussed the Oracle JVM and its transient nature and association with a
specific Oracle session. Also, we talked about the Oracle JVM sandbox.
