Home arrow Oracle arrow Transaction Statements in Oracle and PHP

Transaction Statements in Oracle and PHP

In this second part of a two-part series on how to properly handle transactions with Oracle databases, you'll learn how to use rollback statments, the savepoint statement, and how to complete transactions with PHP. This article is excerpted from chapter 33 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

TABLE OF CONTENTS:
  1. Transaction Statements in Oracle and PHP
  2. The SAVEPOINT Statement
  3. Performing Transactions Using PHP
By: Apress Publishing
Rating: starstarstarstarstar / 1
February 24, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Implicit COMMIT Statement

A common mistake you can make when building a transaction is to perform an implicit COMMIT—in other words, a SQL statement that automatically commits your changes, usually when you do not want to. All DDL statements perform an implicitCOMMIT, such asCREATE TABLEorALTER INDEX. As a general rule, then, do not perform DDL within your transactions. In a typical OLTP (online transaction processing) environment, this should not be a problem; a well-designed end user application will only have DML statements. ImplicitCOMMITstatements are more of a concern when you are issuing statements interactively. Then you must take care and pay attention to what you are doing. If you must perform any DDL for a user application, do it before or after a transaction, not during.

Explicit ROLLBACK Statement

Up to now, we have not used ROLLBACK explicitly; implicitROLLBACKstatements usually occur because of some kind of database, network, or client failure. To elaborate further, an explicitROLLBACK is a great way to change your mind if you make a change to a database table and you do not want to make the change permanent. Running an explicitROLLBACKstatement applies to all DML activity since the lastCOMMITorROLLBACKor since the session began. It can roll back 1 or 1,000 DML statements, limited only by the size of yourUNDOtablespace (we cover theUNDO tablespace in Chapter 29). You can also perform a partialROLLBACK; we tell you more about this in the next section on theSAVEPOINT statement.

For example, your last task for the day is to delete employee number 106 from the database; you run thisDELETEstatement in SQL*Plus:

SQL> delete from employees;

107 rows deleted.

Oops, you forgot theWHERE clause, so all of the employees are deleted. This is not the desired result. However, since you are using Oracle Database XE, you can roll back the deletions usingROLLBACK (and likely save your job in the process):

SQL> rollback;

Rollback complete.

SQL>

TheEMPLOYEEStable is now back to its original state. Any other DML statements executed before theDELETE without an interveningROLLBACKorCOMMITwill also be rolled back. This time you remember theWHERE clause:

SQL> delete from employees where employee_id = 106;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

During your lapse of judgment, probably due to not enough coffee, your users are none the wiser; during the entire session until theCOMMIT, Oracle provided a read-consistent view of theEMPLOYEEStable. In other words, your users were not aware that theEMPLOYEEStable was empty and would not see your changes, unless you were to perform aCOMMITafter the firstDELETE.



 
 
>>> More Oracle Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: