How to access oracle stored procedures in ColdFusion???

I had to write stored procedures and access them on ColdFusion. The problem was I'm new to both technologies. But somehow I was able to find a way to do that. I thought to share that with you.

First of all we have to write the SP. But normal SPs on Oracle cannot be accessed by ColdFusion. So we have to write a package and then implement the procedures inside them. A package contains a specification and a body.

Here is the specification of my package. To make them we just have to run them on SQL++.

 

CREATE OR REPLACE PACKAGE Action_pkg

IS 

TYPE        ref_cur_type

IS        REF CURSOR; 

PROCEDURE GetAction(actionId IN ACTION.ID%TYPE, actions OUT ref_cur_type); 

END Action_pkg;

 

Then here comes the body.

 

CREATE OR REPLACE PACKAGE BODY Action_pkg

IS 

PROCEDURE GetAction(actionId IN ACTION.ID%TYPE, actions OUT ref_cur_type)

IS

BEGIN

OPEN actions

FOR SELECT ACTION.ID,ACTION.CREATIONDATE,ACTIONCATEGORY.NAME

FROM ACTION,ACTIONCATEGORY

WHERE ACTION.ID = actionId AND ACTION.ACTIONCATEGORYID = ACTIONCATEGORY.ID;

END GetAction;

END Action_pkg;

 

Then we have to write the ColdFusion code to execute the SP and get the results.

 

<cfstoredproc procedure="Action_pkg.GetAction" datasource="test_DSN">

    <cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="actionId" type="in" value="1">    

<cfprocresult name="myAction" resultset="1">

</cfstoredproc>

 

I think now it is time to explain what really happens there. (I know these are basics on these technologies)

To return some rows we have to feed the data into a cursor on oracle so I have done it on the SP. Then the cursor is returned back. Then on ColdFusion I have called to the SP with the package name and then we have to send the action ID to get the value. Here the ID is 1. And the results are getting into the variable myAction and we can do anything with data received.

    On the Sp I have done an inner join also with the actioncategory table. I feel it is pretty easy to do them on oracle than on SQL server.