Strong SQL Made Even Stronger: Oracle 10g SQL Enhancements, Part 1

Synopsis. Oracle 10g extends many of the already robust features of Structured Query Language (SQL). This article the first of a series illustrates some of the more intriguing new features of SQL within Oracle, including upgrades to the MERGE command, enhancements to hierarchical query capabilities, and improvements to query and access methods for nested tables.

As a DBA and as an applications developer, I have worked with DB2, Teradata, Informix, Sybase, and SQLServer databases. I have to admit that Oracle’s extensions to standard ANSI Structured Query Language (SQL) have always impressed me as some of the most powerful query and data maintenance tools in the industry. Since I spend a lot of time writing, reviewing, and testing SQL statements for efficiency and elegance, I always get excited when Oracle makes that part of my job easier.

Oracle has made some impressive improvements to this already-robust suite of extensions in Oracle 10g, and I will cover some of the more significant ones in this next series of articles, starting with one of my personal favorites, the MERGE command.

Data Traffic, MERGE Right: MERGE Command Syntax Upgrades

I was thrilled when Oracle 9i introduced the MERGE statement because it acknowledged that there is a constant need to either include new data or update existing information from a common data source, but on a conditional basis. As a recycled mainframe programmer whose first programming experience was with a now almost-defunct 4GL language called FOCUS, I still remember its most powerful feature for updating databases: the MODIFY command, which offered the ability to either create a new entry or update an existing entry in a database table based on a simple set of conditional tests against matching transaction and database values.

The Oracle 9i version of MERGE offered the ability to either insert new rows or update existing values in a table based on matching conditions. Oracle 10g has enhanced the MERGE statement even further: it now allows execution of a combination of INSERT, UPDATE, and DELETE DML commands within one PL/SQL block. This makes for extremely versatile code structures. I will illustrate this new versatility using the following scenario:

The investment management firm that manages your client’s retirement benefits program has provided an Excel spreadsheet that lists all employees that have recently either:

  • joined the 401(k) retirement benefits program
  • made a contribution to their 401(k) accounts since the last file was received
  • terminated their involvement in their 401(k) plans

The Human Resources department has asked this information be applied immediately to the appropriate database tables so that they can determine if there is a downturn in employee participation in the 401(k) retirement program.

To handle this request, I will convert the incoming Excel spreadsheet to a comma-separated values (CSV) file and then create an external table to read the CSV file. Using the enhanced MERGE command, I will then apply those transaction data against the table that contains the status of every employee’s participation in the company’s 401(k) program.

Listing 1.1 shows the transaction table, the external table definition, and the MERGE statement that illustrate this solution.

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles