Product Review

SQL is the bread-and-butter language of relational databases. Although the language has been standardized (SQL-92 and SQL:99), virtually no vendor's implementation strictly conforms to the standards. Most database products use their own syntax (in joins, for example) and, more important, implement several language extensions.

For the developer it's necessary to know about product-specific extensions in order to leverage the database optimally. This is true whatever the nature of the application - Web or client/server. In addition, for the Web programmer knowledge of these extensions can help shift a lot of the processing load off the Web/application server to the database where it belongs (see Ben Forta's comments on this in "Take Your Database Out of Retirement," CFDJ, Vol. 1, issue 3). In this article we present some features of Oracle SQL that we've found useful when developing ColdFusion applications.

This article is separated into two parts. Part 1 explores some of the built-in functions that you have at your disposal when working with Oracle and compares them to the corresponding ColdFusion functions. Part 2 will show how some basic application problems can be solved with SQL, with particular reference to Oracle-specific syntax. This overview is not comprehensive, but it should help the Oracle novice find his or her way around the database by pointing out features that work particularly well in ColdFusion application development.

Leveraging Oracle Functions in ColdFusion
This section presents functions that Oracle delivers "out of the box." Oracle also lets you define your own functions (analogous to custom tags in CF), but this process will be explained in a later article. For the moment, keep in mind that this discussion is just the tip of the iceberg.

For quick reference, we've listed ColdFusion string, numeric, and date functions with their Oracle counterparts in Tables 1, 2 and 3, respectively. Many ColdFusion functions have no direct counterpart in Oracle and vice versa. However, as you'll see in many of the following examples, much of what can be achieved with ColdFusion functions can also be achieved by using one or more Oracle functions in tandem.

Many examples use an Oracle table called DUAL. This is a single row table that contains a dummy entry (as can be seen by selecting * from DUAL). The table is owned by SYS (which is the user with the most privileges in Oracle installations), and most Oracle installations make it available to all users through a public synonym. Selecting from DUAL is a useful technique when you want to have Oracle do a computation and return the result to you.

Invoking Oracle Functions in ColdFusion
As you may be aware, ColdFusion doesn't allow you to invoke Oracle functions directly through its CFSTOREDPROC tag. However, you can make function calls through CFQUERY by simply SELECTing from DUAL. For example:

<cfquery name="yourFunction"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
yourFunction(arguments) return_value
from
dual

</cfquery>

The variable yourFunction.return_ value[1] contains the returned value.

A Simple Audit Trail Using USER and SYSDATE
It's extremely useful to keep track of modifications (inserts or updates) to tables. One simple way to do this is to add four audit columns to any table whose data movements you wish to keep track of. CREATION_USER keeps track of the user that created the record; CREATION_DATE, the date the record was created; MODIFICATION_USER, the last user who modified the record; and MODIFICATION_DATE, which stores the date the record was last modified. Then, when carrying out an insert or an update, you merely insert (or update) these four (or two) columns using the Oracle functions USER and SYSDATE, respectively.

The insert in Listing 1 enters the current account user and system date (down to the second) into the four columns. These columns can then be used at any time to keep track of who made modifications to your data and when.

It's also worthwhile noting here that if you need extensive auditing of your data, that is, keeping exact track of any and all modifications to your data, Oracle has an advanced auditing system built into the RDBMS. The audit trail produced by the built-in system not only allows you to audit data, but also user sessions. A detailed discussion of the Oracle audit trail is beyond the scope of this article, but it's important for you as a CF developer to know that it exists in case you need advanced auditing capabilities. Inserting Dates into Oracle

As we're sure many readers have experienced, inserting dates into an Oracle database from ColdFusion can be a bit trying. One simple way to do this is to pass date values as strings and convert the string to a date data type using the Oracle TO_DATE function (see Listing 2).

Truncating Dates
An Oracle date data type stores the date and a timestamp. This can get annoying sometimes, since you may want to filter out data based on date only (disregarding the time portion). One way of achieving this is to use the TRUNC function when comparing dates. This function sets the timestamp to midnight, as is made clear when executing the following query:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
to_char(trunc(sysdate),'DD-MM-YYYY
HH24:MI:SS') truncated_date
from
dual

</cfquery>

It's important to note that the second parameter of both TO_DATE and TO_CHAR functions is extremely flexible and easy to use. This parameter, formerly called a date mask, allows you to deal with just about any date/time format you may want to use.

Using DECODE
Did you know that Oracle provides a function that behaves similarly to the CFIF tag? If you didn't, look closely at the DECODE function. What makes DECODE so appealing is that you can use it just about anywhere within a SQL statement. Its syntax is:

DECODE(expression,
evaluated_value_1, value_1,
evaluated_value_2, value_2,
evaluated_value_N,value_N,
default_value)

where DECODE returns one of value_1, value_2,..,value_N, or default_value depending on whether expression evaluates to evaluated_ value_1,evaluated_value_2,..,evaluated_value_N, or some other value (treated as the default).

This function is extremely useful in making dynamic assignments in SQL statements. As an example, assume that we pass a numeric month value (1 through 12) via a ColdFusion variable (form.month) to a select statement and, depending on the value of form.month, we want to select the appropriate month from a table containing monthly values. DECODE sets the column name on the fly (see Listing 3).

With the select statement in Listing 3 you've effectively turned the month into a parameter that causes the database to return data from different columns depending on its value.

The other way to achieve the same result would be to use a CFCASE or CFIF - a tedious and expensive option. DECODE is much more elegant, and it also pushes the task on to the database, thereby conserving your ColdFusion resources. Also note that Listing 3 also shows that the default_value is optional.

Ensuring Consistent Capitalization of Returned Values
Sometimes data is inserted into tables with inconsistent capitalization. For instance, some values in a column may have been entered in uppercase whereas others may be in lower. This can look quite ugly when displayed on a Web page. You can ensure consistent capitalization by using the INITCAP and LOWER functions in tandem.

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
initcap(lower(columnName)) initial_cap
from
yourTable

</cfquery>
Simple String Parsing Using INSTR
and SUBSTR

At times you may want to extract a substring from a character string; however, you only know the characters delimiting the substring, not the numeric start and end positions of the substring. In this case, you can't use the SUBSTR (substring) function directly. However, you can use the INSTR function to get the positions of the delimiting characters and pass these on to the SUBSTR function. An (artificial) example should help make the preceding prose a bit clearer. Here we want to extract the word "whom" from the substring "for.whom.the.bells.toll".

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
substr('for.whom.the.bells.toll', instr('for.whom.the.bells.toll','.',1,1)+1,
instr('for.whom.the.bells.toll','.',1,2)-instr('for.whom.the.bells.toll','.',1,1)-1)
second_string
from
dual

</cfquery>

Yes, we know you can achieve the same thing using the ColdFusion string function GetToken, but, remember, the idea is to stress your database and save your ColdFusion server.

Concatenating Strings
Sometimes it's necessary to concatenate two or more columns from a table into a single quantity for display purposes. One example of this would be concatenating the employee first name, last name, and ID into a single entity for display on a Web page. The easiest way to do this is via the concatenation operator ||, like so:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
employee_ID|| ' ' ||Initcap(lower(first_name)) || ' ' || Initcap(lower(last_name))
emp_name
from
employees

</cfquery>

The query column alias emp_name contains the concatenated information. By the way, the concatenation operator is useful if you want to generate a comma-delimited file for import into Excel, for example. We'll leave this as an exercise for our readers.

Trimming Strings
The Oracle functions LTRIM (left trim) and RTRIM (right trim) are useful in trimming strings. These functions allow you to trim arbitrary characters from the ends of a string, as well as the usual whitespace characters.

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
ltrim(rtrim('abc012cbb','abc'),'abc') trimmed_string
from
dual

</cfquery>

The first argument of these functions is the string to be trimmed and the second is the trim string, which contains the characters to be trimmed out. Thus, the above query returns the string "012" - the characters "a", "b", and "c" are trimmed out from the left and right ends of the string. If you don't specify the second string, the functions assume you want to trim white-space characters - equivalent to the ColdFusion Ltrim and Rtrim functions.

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
ltrim(rtrim(' 012 ')) trimmed_string
from
dual

</cfquery>

This returns the string "012". Again this can be done (but shouldn't be) on the ColdFusion server.

Replacing Characters in a String
Trimming strings is fine, but what if you want to remove characters from the middle of a string, or if you want to replace one substring with another. No problem, Oracle offers you the REPLACE function. Here's an example:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
select
replace('01abc2','abc') processed_string
from
dual

</cfquery>

This removes the "abc" in the middle of the first string. Here's an example in which one string is replaced by another:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
select
replace('01abc2','abc','def') processed_string
from
dual

</cfquery>

This replaces "abc" with "def".

Translating Characters in a String
The REPLACE function looks for an exact match of the replace string. What if you want to replace strings based on a character-by-character translation? For this, use the TRANSLATE function, see the following:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
select
translate('01abc2','abc','def') processed_string
from
dual

</cfquery>

What would happen if you change the string to "01acc2", keeping the replace and translate strings the same? Now that you've tried it, you know what TRANSLATE can do for you.

Arithmetic with Nulls
Database practitioners know that nulls in arithmetic expressions yield null results. However, sometimes we'd like to pretend that null values contribute a known value (usually zero) to a sum. For example, when summing a salary and commission column, where the commission column is nullable and the salary is not, we would like the sum to yield the salary in case the commission is null; that is, we'd like to treat a null commission as a zero value. This can be handled using the NVL function, as seen in this query:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
2+nvl(null,0)
from
dual

</cfquery>

Warning the User About Data Volume
Assume your application has access to some tables with a large number of records, and the specs of the application require that users be permitted to drill down into the data as deep as they want (yes, it's crazy, but unfortunately some clients do demand such functionality). Before executing the (potentially fatal) queries, you may want to give the end user some feedback regarding the damage he or she is about to cause. The most straightforward way of doing this is by counting the number of records before you submit the query:

<cfquery name="countRecs"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
count(*) total_recs
from
...
</cfquery>

However, the number may not be very significant if you're dealing with wide tables with lots of columns or where the data types of the columns are very large, for example, VARCHAR2(2000). You can actually provide users with a good estimate of the volume of data they're going to slosh about over the intranet/Internet by using the VSIZE function as follows:

<cfquery name="dataSize"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
NVL(VSIZE(column_1),0) + NVL(VSIZE(column_2),0) + ... size_in_bytes
from
...
</cfquery>

VSIZE returns the number of bytes in the internal Oracle representation. The above query sums the total value of bytes in the internal representation for all the columns you've included and the records you've selected. Although this won't be exactly the volume of data to be transferred, it will be extremely close (for example, one character is one byte in Oracle internal representation). You can also use the same technique to prevent users from accessing more than a set volume of data.

Oracle SQL Extensions and Idiosyncrasies
Now that you've seen how Oracle SQL functions can be useful in your work, we'll show how you can avoid some complex ColdFusion coding by using some Oracle-specific SQL extensions and concepts.

Pseudo Columns
Pseudo columns behave identically to normal table columns, however, they're not stored anywhere and can't be updated or inserted. You're probably wondering what the last sentence means, so it's probably best to give an example.

One of the pseudo columns that Oracle makes available to every result set is the ROWNUM. For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or a set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. A frequent requirement is to get the top N records in a table. If you combine the ORDER BY clause in a query with a WHERE clause on the ROWNUM pseudo column, you can easily generate top N hit lists. For example:

<cfquery name="topN"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select * from
(select employee_id, salary
from employees order by salary desc)
where
ROWNUM < 11;

</cfquery>

This query returns, very simply, the top 10 salaried employees and their salaries. No additional ColdFusion code is necessary.

This example also shows that the FROM clause doesn't necessarily need to contain a table name; it can even refer to a set of records returned by a subquery.

There's another Oracle pseudo column - ROWID - that's essentially the physical storage address of the row. This can be useful in constructing a SQL statement to detect duplicate rows in a table. We'll leave you to mull over how this can be done.

Outer Joins
Outer joins are useful when you want to display the entire primary key of a reference table, even when the fact table (or referring table) doesn't contain data for all values of the foreign key. A simple example may be useful: consider a PRODUCT table with columns PRODUCT_ID and DESCRIPTION, and a SALES table with columns PRODUCT_ID and VALUE_SOLD. You may want to create a report that displays sales for all available products, including those for which no sales have been made. (Listing 4 shows an outer join.)

The first thing to notice in Listing 4 is the syntax for the outer join, which could be quite different from what you're used to in other database products. The principle is the same, though: the (+) on the SALES side of the join tells Oracle that you want all products returned, including those for which no sales have been made.

Bill of Materials (Hierarchy) Problems
In relational databases, hierarchies are usually represented through self-referential tables - the foreign key refers to the primary key of the same table. An example would be an EMPLOYEES table with EMP_ID as the primary key and MANAGER_ID as the foreign key (referring to EMP_ID). To decompose the hierarchy, join the table to itself as many times as there are levels in the hierarchy. However, the number of levels is usually not known beforehand. Oracle provides an easy way to deal with such a situation through its nonstandard START WITH...CONNECT BY PRIOR clauses (see Listing 5).

The query assumes that the top person in the hierarchy has no manager (manager_id is NULL). Note the use of the LPAD function to offset the records depending on the hierarchy level. The pseudo column LEVEL is always returned by such a query, and, as the name suggests, denotes the hierarchy level of the record.

Locking Records
A problem often encountered when developing a system that allows a user to update or delete data in database tables via the browser is that you want to make sure the user is the only one working on the records. Usually a CF developer will call up the records in question with a SELECT statement and then, based on user input, these records will be modified with a DELETE or UPDATE statement. However, if the records are not locked, another user may modify the data through another session and thus the current user is modifying "old" data.

There are many ways of dealing with such problems, often depending on the context of the transactions in question. One of the simplest ways is to use the SELECTŠFOR UPDATE command. When you write your CFQUERY tag to call up the required records for display, add the FOR UPDATE command at the end of the select statement (see Listing 6). The result is that other database sessions will only be able to view the records in question, not modify them. The FOR UPDATE clause has placed a lock on the complete record set. Only the current user's session will be able to process an UPDATE or DELETE statement on the records. Any other session that attempts to modify the same data will automatically be forced to wait by Oracle. To release the locks and make the data available to other database sessions for modification, you need to send a COMMIT or close the current user's database session. Be careful when using such statements: make sure you lock only the necessary records using the WHERE clause. If not, you may accidentally cause other users to wait for COMMITs on data they're not interested in.

Conclusion
At this point we'd like to wrap up our introductory article in this series about using features of the Oracle RDBMS in your ColdFusion applications. If you haven't yet looked at Oracle in detail, we hope we've managed to arouse your curiosity. The Oracle RDBMS is an extremely complex and efficient engine for storing and manipulating data, and it goes a long way in bringing about the principles set out by Dr. Codd, the founder of relational database theory.

As a ColdFusion developer you're in the privileged position of being able to pick and choose how best to utilize Oracle's rich feature set within your Internet applications. If you're clever about your choices, your applications will go into production quicker and run faster with fewer resources. Learn as much as you can about the database you're using - your users will thank you for your efforts.

By the way, one of the best sources for Oracle information is the Oracle developer's Web site at http://technet.oracle.com, and the free membership provides access to countless resources. Among the most important is the complete documentation for the Oracle RDBMS. You can view it online or download it in PDF format. Like any technical documentation it isn't exactly an inspiring read. However, it's comprehensive, well organized, and chock-full of examples, and is definitely your best bet for information on Oracle.

In Part 2 we'll delve into the procedural extensions that Oracle provides to SQL within the database: PL/SQL and Java.

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...