Putting Oracle SQL To Work in Your ColdFusion Applications, by Kailasnath Awati & Mario Techera

Volume 3, Issue 6
Listing 1

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


  insert into
     yourTable
     (column_1,
     ....
     creation_user,
     creation_date,
     modification_user,
     modification_date)
  values
     (column_1_value,
     ...
     USER,
     SYSDATE,
     USER,
     SYSDATE)


</cfquery>


Listing 2
<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>


Listing 3
<cfquery name="monthlyValue"
  datasource="yourDSN"
  username="yourUsername"
  password="yourPwd">


  select
     decode(#form.month#,
         1,january_value,
         2, february_value,
         3, march_value,
         4, april_value,
         5, may_value,
        6, june_value,
        7, july_value,
        8,august_value,
        9,september_value,
        10, october_value,
         11, november_value
        12,december_value) monthly_value
     from
        monthly_values
     where
         entry_id=#form.id#


</cfquery>


Listing 4
<cfquery name="sales"
  datasource="yourDSN"
  username="yourUsername"
  password="yourPwd">
select
  t1.product_id "product number",
  t1.description "description",
  t2.value_sold "sales"
from
  product t1,
  sales t2
where
  t1.product_id=t2.product_id (+)


</cfquery>


Listing 5
<cfquery name="employeeHierarchy"
  datasource="yourDSN"
  username="yourUsername"
  password="yourPwd">


  select
    lpad(' ',2*(level-1)) | |name
  from
    employees
  start with
    manager_id is null
  connect by prior
    emp_id=manager_id


</cfquery>


Listing 6
<cfquery name="Lock Records"
  datasource="yourDSN"
  username="yourUsername"
  password="yourPwd">


select
employee_id,
salary
from
employees
order by
salary desc
for update


</cfquery>