Center for Information Technology Services

The following information and syntax will help web developers to write SQL for ORACLE databases. Please review the information below and if you have any questions or need further information, please contact the Web Development department.

Example using CFQueryParam and Oracle Databases to prevent SQL Injections.

ORACLE:
There are certain differences between Oracle and SQL Server Queries. Here is the correct Oracle syntax:

When selecting the top 1 row:
SQL Server: SELECT top 1 from tableName
Oracle: select max(tableid) as tableid from tableName

When inserting/updating data in a datetime field, here is the format for the field:
Note, you can split it up for either just the date information, time information, or both (so you can enter just date with no time, just time with no date, or enter the full command).
SQL Server: '02/20/02 02:00 PM" Oracle: to_date('02/20/02 02:00 PM','MM-DD-YY HH:MI PM')

When inserting data with an apostrophy (') (example, "Richard's Event" as the title from a Form) use the following format:
#REReplace(PreserveSingleQuotes(Form.field),"'","''","ALL")# 

When selecting data from Oracle, use the following SQL code to make the search case-insensitive:
SQL Code: SELECT * from myTable where
upper(description) like '%#UCASE(Form.keyword)#%'

When trying to do a JOIN in Oracle, use the (+) command:
SQL Code: SELECT fieldNames FROM tableOne,tableTwo
WHERE tableOne.CLASS_ID = tableTwo.CLASS_ID(+)