not able to call Stroed procedure in MX

Hi All,

We are facing one problem in cold fusion, attached is the code.
In the below code, We call the stored procedure through Cold Fusion page and
this was working fine in Version 5, but same is not working in MX 6.0 version.
The only problem coming becuase Out/return type is an Array.If we make it String then it works

We also tried Cfprocresult this also does not work.

Below error comes when we call this
Error Executing - Database Query (Macromedia) [Oracle JDBC Driver] Oracle ORA - 06550:line1 ,column1:PLS-00306: wrong number of types of arguments in call to 'LOGON' ORA-06550:line1,column7:PL/SQL Statement ignored
The error occurred on line 18.

CODE IS

<cfstoredproc datasource="#appDataSource#"
procedure="CF_SECURITY_PKG.LogOn"
returncode="No">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="In" value="
#Form.txtUserid#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="In" value="
#form.txtPassword#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="In" value="
#CGI.REMOTE_ADDR#">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="In" value="WEB
APPLICATIONS">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" type="Out" variable="vUserInfo">
</cfstoredproc>

Any help in this regard will be greatly appriciated.

Regards
Vivek

Are you using CFMX 6.1 with the latest patches? 6.0 may have had issues.

Though from the look of the error and your code it is either in the PL/SQL or in the way you are calling the procedure. I notice you are not specifying the "variable" attribute for each cfprocparam tag, you could try that. Also confirm that the data types for each parameter are correct.

Other than that all I can say is "trial and error". I know that PL/SQL stored procedures work in CFMX 6.1 so it has to be something in the way it's being called or the procedure itself.

thanks kiteless,

We will try the option you suggested. we are using MX 6.1 only. what is the difference between MX 6.1 or MX 6.1 with patches.

One thing I would say same code, with same stored procedure is working fine in version 5 but it is not working in version MX 6.1

There are several hotfixes out for CFMX 6.1. Check the CF page at Macromedia.

And due to the switch to Java there is occasionally code that ran on 5 that doesn't work the same on CFMX, mainly due to MX's conversion under the hood to Java datatypes, using JDBC instead of ODBC, etc. CFMX can be more fickle with data types so be extra careful when specifying the data types in your stored proc.