CFStoredProc stopped working

[mikedc16]

I am currently upgrading to CF MX7 Enterprise from CF MX Enterprise. I have a
CFStoredProc tag that works on MX but does not work on MX 7. The CFML and
Oracle function are included below. The database is Oracle 9i. I am calling an
Oracle function located in a package. I get the following error:
"[Macromedia][Oracle JDBC Driver]Incorrect parameter bindings for stored
procedure call. Check your bindings against the stored procedure's parameters. "

Any help would be greatly appreciated.

CFML:
<cfstoredproc procedure="pk_tom_project.find_sat" datasource="ultra"
debug="yes">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#thedate#" null="no">
<cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable="v_sat">
</cfstoredproc>

Oracle:
FUNCTION find_sat (t_date IN VARCHAR2)
RETURN VARCHAR2
IS
v_dat VARCHAR2 (3);
v_sat VARCHAR2 (15);
BEGIN
v_dat := TO_CHAR (TO_DATE (t_date), 'DY');
IF v_dat = 'SAT'
THEN
v_sat := t_date;
ELSE
SELECT NEXT_DAY (t_date, 'SAT')
INTO v_sat
FROM DUAL;
END IF;
RETURN v_sat;
END find_sat;

[paross1]

You cannot call an Oracle stored function from ColdFusion using cfstoredproc,
as CF has no way of handling the "return value" of an Oracle function. You can
only call Oracle stored procedures using cfstoredproc. This is how it has been
since CF 4.5, unless something has changed recently. If you need a value
returned from a procedure, it must be through a cfprocparam, unless you need a
result set, in which case the data is returned via a reference cursor and
cfprocresult.

Phil

[paross1]

OK, it looks like "they" made some changes with MX that now allows you to call
Oracle functions. Your return value will be contained in a variable called
cfstoredproc.statusCode, so you would no longer use a cfprocparam of type OUT
to retrieve this value. You would still have your IN cfprocparam, then capture
your return value in cfstoredproc.statusCode.

<cfstoredproc procedure="pk_tom_project.find_sat" datasource="ultra"
debug="yes">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#thedate#"
null="no"></cfstoredproc>

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

Phil

[mikedc16]

Well, that got rid of that error, but exposed a new one "[Macromedia][Oracle
JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character to
number conversion error ORA-06512: at line 1 " which seems to indicate that the
response from the function won't fit in the status code variable.

[paross1]

Well, there is always plan B - change the function to a procedure, and return
the value as an OUT parameter instead of a RETURN value. That is what I have
always done, since I have been using Oracle stored procedures for years, and
had to do this with ColdFusion 4.5 anyway.

Phil

[lxa91dw]

Unless there is some reason that you need to use a function or proc, why not
just use a sql statement like the following:
select decode(TO_CHAR (TO_DATE ('t_date'), 'DY'), 'SAT', 't_date', NEXT_DAY
('t_date', 'SAT')) "V_SAT" from DUAL
or even simpler
select NEXT_DAY (to_date('t_date')-1, 'SAT') "V_SAT" from DUAL

[mikedc16]

We about 40 functions that we use that I would rather not have to rewrite as
procedures. I was kind of hoping that someone would have some insight as to why
the functionality has gone away with MX 7, when usually functionality is
something that gets added with a new release. It's hard to believe that we are
the only ones who are using functions.

[jsantelli]

Did you ever find a soltuion to your problem?