I was recently asked for an small example to create an Oracle stored procedure and a ColdFusion page to run it, so for the benefit of anyone else looking for a simple example for testing, you can download it here.

  • put the .sql file into the Oracle bin directory, then run the script from SQL Plus to create the sample procedure.
    Just enter: SQL> @cMacrSampleProc.sql
  • edit the datasource name in the cfm file
  • run the cfm to test using CFSTOREDPROC and CFPROCPARAM

Oracle Stored Procedure

set echo on
-- *** @cMacrSampleProc.sql ***
-- *** NOTE: Logon using the SCOTT schema ***
spool MACR_SAMPLE_PROC.txt
-- ***
-- *** Create script for PACKAGE: MACR_SAMPLE_PROC ***
-- ***
CREATE OR REPLACE PACKAGE MACR_SAMPLE_PROC AS

PROCEDURE sampleproc(
pParam1 in integer,
pParam2 out varchar2);
END MACR_SAMPLE_PROC;

/
CREATE OR REPLACE PACKAGE BODY MACR_SAMPLE_PROC AS
PROCEDURE SampleProc(
pParam1 in integer,
pParam2 out varchar2) IS
BEGIN
IF pParam1 = 1
THEN
pParam2 := 'Hello !!';
ELSE
pParam2 := 'Goodbye !!';
END IF;
END SampleProc;

END MACR_SAMPLE_PROC ;
/

-- ********************* VERIFY PACKAGE ****************************
set echo off
set pages 80
set lines 80
set heading on
column line format 9999
column name format a20
column object_name format a20
column object_type format a15
column position format 9999
column status format a10
column text format a30
--
select object_name, object_type, status
from user_objects
where object_name =
(UPPER('MACR_SAMPLE_PROC')) ;
set echo on
-- ********************* CHECK ERROR FILE **************************
set echo off
select name, line, position, text
from user_errors
where name =
(UPPER('MACR_SAMPLE_PROC')) ;
spool off


CFSTOREDPROC

[cfstoredproc procedure="SCOTT.MACR_SAMPLE_PROC.SAMPLEPROC"
datasource="YOUR_ORACLE_DSN"
RETURNCODE="no">


[cfprocparam type="In"
cfsqltype="CF_SQL_INTEGER"
variable="OneIsHello_TwoIsBye"
value="1">


[cfprocparam type="Out"
cfsqltype="CF_SQL_VARCHAR"
variable="HiOrBye">


[/cfstoredproc>

[cfdump var="#HiOrBye#"/>


For a great article with much more depth about using Stored Procedures with ColdFusion and Oracle, see:
Learning Stored Procedure Basics in ColdFusion MX
http://www.macromedia.com/devnet/coldfusion/articles/stored_procs.html