Help with calling Oracle Procedure

Report · Oct 19, 2007

First, let me state that I'm new to Oracle and using it with ColdFusion, but I've used CF on and off for the past 6 years. So this might be a simple "newbie" problem with Oracle and CF.

My boss wrote the following simple Oracle procedure to concatenate three variables together (I know I could do this in CF directly, but I need to be able to use his existing procedures in Oracle)

SPEC:
function ShowCaption(inprecaption in long,
incaption in long,
inpostcaption in long,
method in varchar2,
xcoord in number,
ycoord in number)
return long;

BODY:
function ShowCaption(
inprecaption in long,
incaption in long,
inpostcaption in long,
method in varchar2,
xcoord in number,
ycoord in number)
return long
is
begin
return inprecaption || incaption || inpostcaption;

end ShowCaption;

I'm trying to call that procedure using the attached code but when I run this code I receive the error "ORA-01008: not all variables bound" and I'm not sure why. Searching Google hasn't come up with anything and I've used other procedures, but can't seem to make this one work. I know the procedure works within Oracle as I'm able to run it.

I would really appreciate any ideas.

Report · Oct 22, 2007

Since he wrote his PL/SQL as a FUNCTION instead of a PROCEDURE, it complicates returning the result to ColdFusion. You haven't provided any means of capturing the return value of your function call in your CFSTOREDPROC call. If he had declared this a procedure, then you probably would have added an OUT parameter to the PL/SQL and an OUT CFPROCRESULT tag, but since it is a function, you are going to have to capture the result differently.

Try adding a returncode="yes" parameter to your CFSTOREDPROC tag, then check the cfstoredproc.statusCode variable for your returned result value:

<cfoutput>#cfstoredproc.statusCode#</cfoutput>

Normally, I avoid calling Oracle functions from ColdFusion because in older versions of CF, there was no way of capturing the return value, so I rarely ever do it this way. In other words, I strictly write PL/SQL procedures for use with ColdFusion. However, if you are required to work with an existing function, then the method mentioned above may work for you.

Phil

Report · Oct 22, 2007

Could I create a procedure that calls this function and has an OUT variable to return the value?

If we are going to go with CF to replace our existing web front-end, I have to be able to re-use as much of my bosses existing Oracle code, no matter what.

Report · Oct 22, 2007

quote:

Could I create a procedure that calls this function and has an OUT variable to return the value?

That is always an option if you can't alter the original function.

Phil

Report · Oct 25, 2007

There is actually a rather straightforward method to call an Oracle PL/SQL function which I would recommend as an alternative to the already suggested fix.

You can actually call any Oracle function (whether provided by Oracle or user defined) directly inside of a SQL query. So, instead of using <cfstoredproc.... Just do a straight <cfquery... It would look something like this:

<cfquery name="myquery" datasource=......>
select ShowCaption(param1, param2, param3...) from dual
</cfquery>

<cfdump var="#myquery#">

Couple notes:
1. The query form "Select function_call from dual" is actually a very standard way to call some functions in Oracle. The "dual" table is a dummy table which is guaranteed to be inside of every Oracle database and contains exactly 1 record.

2. I would suggest always prefacing all table names, user defined functions, user defined packages, and user defined procedures with the Schema name that they reside in.