Using Oracle Functions inside of CFMX

[jwdcfdeveloper]

I need to know how do I execute an Oracle (9i) function inside of a CFMX page. I know how to use functions Selecting from dual:

<cfquery name="myQuery" datasource="ds">
Select My_Function(#cfVar#) From dual
</cfquery>

However, the function I am using calls another function inside which updates a table. The internal function (let's call it update_table(numberVar) for argument sake) does not work when using a select statement. Is there a way to execute (e.g. Using the EXEC or EXECUTE statement) this function so that the update_table runs as well as the My_Function function?

 

[TruthInSatire]

how about cfstoredproc?

http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/Tags-pt39.htm

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[jwdcfdeveloper]

I am trying cfstoredproc however when I use it I get this error:

ORA-01008: not all variables bound

I know you can get this error if the variable isn't set, I know this isn't true because I am using a session variable, and there are 6 other queries that use the value before the value is used in this one, I know my connection to the datasource connection is ok, and the server is working.

Any other ideas about what this means?

 

[TruthInSatire]

I don't work with oracle enough to guess. Maybe rudy can offer a suggestion. Or try the oracle forum.

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[jwdcfdeveloper]

I don't know if this totally an oracle issue. I spent some more time with my code and cleaned up the way I was setting certain session variables. Here is the cfstoredproc tag that sometimes errors out:

<cfstoredproc
datasource="ds"
procedure="Discount">
<cfprocparam type="in"
cfsqltype="cf_sql_varchar"
value="#Session.orderId#"
dbvarname="p_var" >
</cfstoredproc>

This function returns the total coupon discount amount. I don't really use the number anywhere, but the important thing is that it updates another table behind the scenes. Once this table is updated I can then use another function to get the discount amount which I display and insert into yet another table. I cleaned up the way the Session.orderId is getting set and I don't see the ora-01008 errors nearly as much, but I had to delete all previous test data from the database for the procedure to work correctly. I am trying to find out the following:

1. Do I have this cfstoredproc set up correctly (i.e. I am using the session.orderid as the input parameter and don't need to return anything)?
2. What could be causing the ora-01008 errors now (as I said I had to re-write the way the session.orderid was set and that helped clear up alot of the errors.

Any help would be greatly appreciated.