Oracle stored procedures and ColdFusion

I’ve heard for years that using Oracle’s stored procedures is both more efficient and more secure than writing queries against the database. It turns out, not everyone agrees with that and there is quite a bit of room for debate (most of the articles focus on MS-SQL Server and T-SQL, not Oracle and PL/SQL, although some general principles still apply):

• Faster Performance using Oracle Stored Procedures
• Stored Procedures vs. Ad-Hoc SQL
• To SP or not to SP in SQL Server
• Stored procedures are bad, m’kay?

I was still interested in how to use Oracle stored procedures with ColdFusion, though.

First I created a simple table and populated it with some data:

id	name
1	Chris
2	Ben
3	Dave

Then I added a stored procedure to Oracle:

CREATE OR REPLACE PROCEDURE testselect 
    (recordset OUT SYS_REFCURSOR) IS
BEGIN
    OPEN recordset FOR
        SELECT id,name
        FROM myschema.name_table;
END;
/

It took me a while to get the stored procedure to compile, but with some Google-sleuthing I eventually cobbled together something that worked.

Now, how to get the data out via ColdFusion? First I added the proper data source to the CF administrator. Then I put the following code in a CFM page:

<cfstoredproc datasource="sptest" procedure="testselect">
    <cfprocresult name="data"/>
</cfstoredproc>
<cfdump var="#data#">

Voila! It worked–3 rows returned.

But what if I wanted to select a subset of rows (or otherwise include a parameter in the query)? First I needed to update the stored procedure to accept a parameter:

CREATE OR REPLACE PROCEDURE myschema.testselect 
    (p_name IN varchar2, recordset OUT SYS_REFCURSOR) IS
BEGIN
    OPEN recordset FOR
        SELECT id,name
        FROM myschema.test_table
        WHERE name = p_name;
END;
/

Next, I needed to include a parameter in my stored procedure call:

<cfstoredproc datasource="sptest" procedure="testselect">
     <cfprocresult name="data"/>
     <cfprocparam value="Chris" dbvarname="p_name" cfsqltype="cf_sql_varchar" maxlength="50"/>
</cfstoredproc>
<cfdump var="#data#">

Voila! Only a single row returned.

Now that I’ve figured out how to use them, I just need to consider the many arguments for and against using stored procedures and decide whether or not I should use them.