Home arrow Oracle arrow Page 3 - Transaction Statements in Oracle and PHP

Performing Transactions Using PHP - Oracle

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).

  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



Using the transactional features in OCI8 is fairly straightforward, relying on a small handful of function calls: oci_connect(),oci_close(),oci_execute(),oci_commit(), andoci_rollback(). You have seen three of these function calls before; in this section, we revisit them from a transactional perspective.

When you connect to the database usingoci_connect(), you are implicitly starting a transaction. None of the parameters ofoci_connect(), however, dictate how your transactions are processed; theoci_execute() function call is where all the transactional fun begins. As you may remember from Chapter 32, the syntax ofoci_execute()is as follows:

bool oci_execute ( resource statement [, int mode] )

Themodeparameter is typically one of two values:

  1. OCI_DEFAULT: The DML statement does not automatically execute aCOMMITstatement upon successful execution.
  2. OCI_COMMIT_ON_SUCCESS: The DML statement will automatically perform aCOMMIT upon successful execution of the statement.

The default value formode isOCI_COMMIT_ON_SUCCESS, which is a bit counterintuitive; you might expect that the default would beOCI_DEFAULT. This is one of the things aboutoci_execute()that you just have to remember: the default is notOCI_DEFAULT. In all of the examples up to this point, you usedoci_execute()without the second parameter, and therefore automatically committed any changes to the database by the statement specified in the first parameter ofoci_execute().

When you are done with the transaction, you can either commit the transaction withoci_commit()or roll it back withoci_rollback(). Both functions use the connection handle fromoci_connect(); as a result, you can have several ongoing transactions within the same PHP script—one active transaction per connection handle.

Revisiting the example earlier in the chapter, we use a PHP script to update two more salaries in the database as a single transaction. In the example in Listing 33-1, you increase the monthly salary by $1,000 for employee number 100 and decrease the monthly salary by the same amount for employee number 102.

Listing 33-1. PHP Code to Perform Two Salary Updates As a Single Transaction (db_update_salary.php)


  // Update salaries: increase one salary and decrease
  // another as a single transaction.

  // Connect to the server and select the database
  $c = @oci_connect('hr', 'hr', '//localhost/xe')
            or die("Could not connect to Oracle server");

  // Show salaries before update.
  echo "Previous salaries: <br />";
  $s = oci_parse($c, 'select employee_id, last_name, salary from employees'
                      . ' where employee_id in (100,102)');

  oci_execute($s, OCI_DEFAULT);
  while ($res = oci_fetch_array($s)) {
     echo $res['EMPLOYEE_ID'] . ' -- ' . $res['LAST_NAME']
           . ': ' . $res['SALARY'] . "<br />";

  // add $1000 to first employee's salary

  $s = oci_parse($c, "update employees
                       set salary = salary + 1000
                       where employee_id = 100");                       

  $result = oci_execute($s, OCI_DEFAULT);

  // subtract $1000 from second employee's salary

  $s = oci_parse($c, "update employees
                       set salary = salary - 1000
                       where employee_id = 102");

  $result = oci_execute($s, OCI_DEFAULT);

  // end of transaction

  // Show salaries after update.

  echo "<br /><br /><br />New salaries: <br />";
  $s = oci_parse($c, 'select employee_id, last_name, salary from employees'
                      . ' where employee_id in (100,102)');

  oci_execute($s, OCI_DEFAULT);
  while ($res = oci_fetch_array($s)) {
     echo $res['EMPLOYEE_ID'] . ' -- ' . $res['LAST_NAME']
           . ': ' . $res['SALARY'] . "<br />";

  // done. If there are any uncommitted transactions, oci_close()
  // will roll back.


Any script or database failure up until theoci_commit()statement will force the execution of aROLLBACKstatement for bothUPDATEstatements. The output of this script is in Figure 33-3. Notice that you must useOCI_DEFAULTeven if you are only displaying rows, otherwise the commit will occur prematurely. In the example in Listing 33-1, you run aSELECT before the transaction begins, and you do not run anotherSELECTuntil after the transaction is complete. UsingOCI_DEFAULTis not required for theSELECTstatements in this example, but it is good practice to useOCI_DEFAULTevery time you runoci_execute()in a transaction-based application to ensure you do not accidentally perform aCOMMIT statement when you do not want to.

Note  There is no explicitSAVEPOINT in OCI8 or PDO. It is, however, fully supported in PEAR/MDB2 starting with version 1.0.0.

Finally,oci_close()or the termination of the PHP script will automatically roll back any uncommitted transactions; as a result, it is good programming practice to explicitly commit or roll back your transactions, otherwiseoci_close()will roll them back when you might not want to.

Figure 33-3.  Results of salary update transaction processing


Now that you’ve read this chapter, you should possess a thorough understanding of transactions, how they’re implemented in Oracle Database XE, and how to incorporate them into your PHP applications. Using transactions in Oracle boils down to three statements: COMMIT,ROLLBACK, andSAVEPOINT. Some statements, such as Oracle DDL statements, force the execution of aCOMMITstatement; these should be avoided when you’re in the middle of a transaction. From a PHP perspective, the OCI8 transaction-related function calls give you all the tools you need to create robust Web applications. 

Database transactions are of immense use when modeling your business processes because they help to ensure the integrity of your organization’s most valuable asset: its information and the relationships between different categories and hierarchies of information in your database such as orders with order items and employees within departments. If you use database transactions prudently, they are a great asset when building database-driven applications.

In the next chapter, we delve more deeply into theSELECTstatement and how to craft a query for any requirement, including, of course, how to use the results of these queries in a PHP application. 

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: