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

The SAVEPOINT Statement - 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 SAVEPOINT statement, you can mark a spot in the middle of a transaction to which you might wish to roll back. ASAVEPOINTstatement allows you to undo part of a transaction rather than all of it. Following is the syntax to use in creating a savepoint:

SAVEPOINT savepoint_name;

You uniquely name the savepoint and then may subsequently reference it in aROLLBACK statement as follows:


Regardless of how many savepoints exist within a transaction, aROLLBACK statement without aSAVEPOINTreference will automatically roll back the entire transaction. The following SQL*Plus example uses a savepoint to conditionally undo the DML statements since theSAVEPOINTstatement was issued. First, the example creates a new region in theREGIONStable because you are opening a branch office in Antarctica:

SQL> insert into regions (region_id, region_name)
2  values (5, 'Antarctica');

1 row created.

SQL> savepoint new_region;

Savepoint created.

The savepointNEW_REGIONmay come in handy later if you need to roll back the insertion of the new countries you will be adding in the next few statements:

SQL> insert into countries (country_id, country_name, region_id)
  2 values ('NA', 'Northern Antarctica', 5);

1 row created.

SQL> savepoint new_country_1;

Savepoint created.

SQL> insert into countries (country_id, country_name, region_id)
  2  values ('SA', 'Southern Antarctica', 5);

1 row created.

SQL> savepoint new_country_2;

Savepoint created.

After performing these two inserts, you realize that you have a geographic anomaly on your hands, and that for now you are just going to add one country to theCOUNTRIEStable but keep the new region in theREGIONStable:

SQL> rollback to new_region;

Rollback complete.

SQL> insert into countries (country_id, country_name, region_id)
  2  values ('IA', 'Inner Circle of Antarctica', 5);

1 row created.

Just to double-check your work before you run theCOMMITstatement, you query theREGIONS andCOUNTRIEStables:

SQL> select * from regions;

---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Antarctica

SQL> select * from countries;

CO COUNTRY_NAME                    REGION_ID
-- ------------------------------ ----------

AR Argentina                               2 AU Australia                               3 BE Belgium                                 1 BR Brazil                                  2 CA Canada                                  2 CH Switzerland                             1 CN China                                   3 DE Germany                                 1 DK Denmark                                 1 EG Egypt                                   4 FR France                                  1

CO COUNTRY_NAME                    REGION_ID
-- ------------------------------ ----------HK HongKong                                3 IA Inner Circle of Antarctica              5 IL Israel                                  4 IN India                                   3 IT Italy                                   1 JP Japan                                   3 KW Kuwait                                  4 MX Mexico                                  2 NG Nigeria                                 4 NL Netherlands                             1 SG Singapore                               3

CO COUNTRY_NAME                    REGION_ID
-- ------------------------------ ----------
UK United Kingdom                          1 US United States of America                2 ZM Zambia                                  4 ZW Zimbabwe                                4

26 rows selected.


Now that everything looks good, you commit the single-row change to theREGIONStable, and the single-row change to theCOUNTRIES table:

SQL> commit;

Commit complete.

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