Orafusion.com: Oracle SQL Tips

SQL Tips:

Note: The Oracle-related material presented here is largely ColdFusion-version independent. However, please note that most of the ColdFusion code has been developed and tested on older versions of ColdFusion (4.0 through 6.0). Consequently, some CFML code snippets may need to be modified to work optimally on more recent versions of the product.

Search using regular expressions (Oracle 10g):

Oracle 10g includes regular expression capabilities right within SQL. Yes, I know CFML has support for regular expressions, but it is more efficient to do this kind of grunt work in the database. Here are some examples of this new functionality. The examples use the ubiquitous SCOTT.EMP table:

<cfquery name="RegExpExample1"
   datasource="yourDSN"
   username="scott"
   password="tiger">

   select
      ename
   from
      emp
   where
     regexp_like(ename,'^[A-E]L')

</cfquery>

This query gets all employee names that start with A, B or C and have L as the second letter. One can use REGEXP_LIKE wherever the standard LIKE clause is used.

The examples below illustrate the use of regular expression functions analogous to the standard Oracle functions SUBSTR, INSTR and REPLACE:

<cfquery name="RegExpExample2"
   datasource="yourDSN"
   username="scott"
   password="tiger">

   select
      ename, regexp_substr(ename,'[A-E]N$')
   from
      emp

</cfquery>

The above query returns null for all rows except the one containing ALLEN, where it returns the string 'EN'.

<cfquery name="RegExpExample3"
   datasource="yourDSN"
   username="scott"
   password="tiger">

   select
      ename, regexp_instr(ename,'[A-E]N$')
   from
      emp

</cfquery>

The above query returns 0 for all rows except the one containing ALLEN, where it returns 4 (the start location of the string "EN").

<cfquery name="RegExpExample4"
   datasource="yourDSN"
   username="scott"
   password="tiger">

   select
      ename, regexp_replace(ename,'[A-E]N$','YN')
   from
      emp

</cfquery>

The above query returns all rows, replacing ALLEN by ALLYN and other rows unchanged.

Converting relational data to XML format (Oracle 9i+):

There are custom CF tags available to convert queries to XML. However, as always, it is more efficient to let the database do the work instead of stressing your CF server. Oracle 9i offers SQL extensions that allow you to return table rows in XML format. Here's a query that returns all rows of the SCOTT.EMP table as XML elements:

<cfquery name="XMLquery"
   datasource="yourDSN"
   username="scott"
   password="tiger">

   select
      XMLELEMENT (emp, XMLFOREST(empno, ename,job)).getclobval() xmlelement
   from
      emp

</cfquery>

XMLELEMENT and XMLFOREST functions are Oracle extensions to SQL. The function getclobval() converts the Oracle XMLType datatype to a string.

The query returns each row of the scott.emp table with column data wrapped in xml subtags named for each column.I've tested the above query with the Macromedia supplied driver and the Oracle supplied thin driver.

If you want to generate an XML file from this data, you need to add the standard XML header and a root node. This is easily done in your CF template as follows:

The above snippet create a variable containing the appropriate header, the opening root node tag (RELATIONAL_TABLE), the query results and the closing root tag.

Finally you can output the results to your browser using CFDUMP and/or write them to an XML file using CFFILE:

The contents of emp.xml can be viewed through an XML enabled browser or text editor.

Notes:
1. One can also write out the xml file directly from the database using Oracle file handling capabilities (UTL_FILE package).
2. XML files can also be created using the built in package DBMS_XMLGEN. You would need to wrap calls to DBMS_XMLGEN in a package and then have CF call the package. I'll write up something on this if there are any requests. Let me know.

Top N analysis (Oracle 8.1+):

In Oracle 8i the FROM clause of a SELECT statement can be a (sub)query. This nice feature allows us to get the top N records of a table with ease. Here's an example that retrieves the top three salaried employees from the EMP table in the scott/tiger demonstration schema of Oracle.

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

   select
      ename employee_name,
      sal salary
   from
      (select
         ename,
         sal
      from
         emp
      order by
         sal desc)
   where
      rownum<=3

</cfquery>

Note that you may not always get the result you might want. For example, if four employees earn the top salary, the query will return only three of them. This problem can be got around using analytic functions (some of which are described below). For a full discussion of top N analysis using analytic functions see the excellent discussion in the book by Tom Kyte: Expert One-on-One Oracle, Wrox Press (2001), pp. 566-575.

Use CASE statement instead of nested DECODE (Oracle 8.1.6+):

Oracle has finally provided a CASE statement which can be used in place of those ugly nested DECODEs. Here's a simple example:

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

   select
      case when
         to_char(sysdate,'D')>'1' and to_char(sysdate,'D')<'7'
      then
         'Weekday'
      else
         'Weekend'
      end as period_of_week    from
      dual

</cfquery>

One can, of course, have any number of "when" clauses. The "else" is optional.

ROLLUP and CUBE clauses to evaluate subtotals and crosstabs (Oracle 8.1.5+):
Oracle 8i SQL has new clauses that allow you to calculate crosstabs and subtotals using a single SQL statement. As an example: say we have a sales table that contains sales information by region, country and city. The following SQL will return intermediate level rolled-up sums in addition to the usual sums specified by the group by clause:

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

   select
      region,
      country,
      city,
      sum(amount),
   from
      sales
   group by
      rollup(region,country,city)

</cfquery>
The rollup is done from right to left, so the order of the columns matters. Using "cube" instead of the "rollup" clause produces a crosstab report.I have tested this feature on Oracle 8.1.6 using both ODBC and native drivers, and it seems to work just fine with both. Note that the rows containing the rolled up totals will have nulls in the rolled up column. To find out more check out my article on Oracle SQL Aggregation Extensions.

Analytic functions (Oracle 8.1.5+):
In addition to the above, Oracle 8i SQL has capabilities to calculate ratios, cumulative totals and several other useful statistics through a single SQL statement. Two examples are shown below - read the docs for details on syntax and much more.

The first example deals with the same sales table as in the previous tip. Here we want to calculate sums by region and country, and the ratio of sales in a country to the total sales in the corresponding region.

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

   select
      region,
      country,
      sum(amount),
      sum(sum(amount))
         over( partition by country),
      ratio_to_report(sum(amount))
         over( partition by country)
   from
      sales
   group by
      region,country

</cfquery>

The second example deals with a table of financial transactions logged over a period of time. We want to report the amount transacted and also keep a running cumulative total to-date. Prior to Oracle 8i this would have to be done via procedural programming. Now its as simple as:

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

   select
      description,
      effective_date,
      amount,
      sum(amount)
         over( order by effective_date
            rows unbounded preceding)
   from
      financial_transactions
   order by
      effective_date

</cfquery>

Check out my article on Analytic SQL to find out more.

Pass dates as strings to Oracle:
Passing date datatype values to Oracle from CF can be problematic. One way around the problem is to pass the date values as strings (varchar2), and convert these to date types using Oracle's to_date function.

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

   insert into
      yourTable
      (col_1,
      date_col)
   values
      (col_1_value,
      to_date(date_col_value,'DD-MM-YYYY')

</cfquery>

In this example date_col_value is a string in the format DD-MM-YYYY.

To insert the current date and time, use Oracle's sysdate :

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

   insert into
      yourTable
      (col_1,
      date_col)
   values
      (col_1_value,
      trunc(sysdate))

</cfquery>

The trunc function, by default, lops off the time part of the system date, leaving only the day, month and year (see the Oracle documentation for further options).

Invoke Oracle functions by selecting from dual:
CF doesnt have a stored function tag. However, one could use a "select from dual" to return a value to CF from an Oracle function like so:

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

   select
      yourFunction(arguments) return_value
   from
      dual

</cfquery>

The CF variable "yourFunction.return_value" now contains the return value of yourFunction.

Back to the top