#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Delhi - India
    Posts
    2
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Delhi - India
    Posts
    2
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    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.

IMN logo majestic logo threadwatch logo seochat tools logo