CFMX7 and Oracle 10G

[CKinnier]

Hi,
I am testing my CF MX7 apps which are currently running atop Oracle 8i and migrating them to 10g. I am unable to establish any
connection at all and not finding much in the way of documentation. Currently setup with ODBC socket but would like to change to Oracle JDBC on the new instance. I have included a sample application.cfm and basic stored procedure call. Any help appreciated.

1. Application.cfm:
<cfapplication name="LipidRegistry" clientmanagement="Yes"
sessionmanagement="Yes"
sessiontimeout="#CreateTimeSpan(0,1,30,0)#"
applicationtimeout="#CreateTimeSpan(1,0,0,0)#"
clientstorage="Cookie">

<cfparam name="request.MainDSN" default="VMTEST">
<cfparam name="request.DBType" default="ORACLE80"> <!-- I think this is where the problem lies-->
<cfparam name="request.Debug" default="yes">
<cfparam name="request.Package" default="irausr.pkg_crrc">

2. Basic stored procedure call:

<cfstoredproc procedure="#request.Package#.proc_set_risk"
datasource="#request.MainDSN#"
username="#session.login#"
password="#session.password#"
dbtype="#request.DBType#"
debug="#request.Debug#">

<cfprocparam type="In"
cfsqltype="CF_SQL_VARCHAR"
variable="patient_mrn"
value="#attributes.v_patient_mrn#"
null="No">

[Drew Nathanson]

Hi,

First of all, make sure that the tnsnames.ora file on the web server has the right information. Second, make sure that you can connect to the server via the adminstrator. If you can't then none of your cold fusion applications will work. As for the JDBC, you can either use the Merant drivers with CFMX 7 or you can use the JDBC drivers from Oracle. If you use the Oracle drivers, you specify them as OTHER and give the path the JDBC drivers.

I hope that helps,

[CKinnier]

Thanks Drew,
This should shed a little more light on the matter. Using an ORACLE JDBC driver was able to verify a connect to the Oracle instance but kept getting a 'Net8 Protocol Error' message. After a bit more research discovered that CFMX 7's drivers are not compatible with an Oracle 10.2 instance when using CFSTOREDPROC but do work with CFQUERY - I was able to test this to be true. So it looks like I will revert to Oracle 10.1 until this is resolved.
Carter

[Guest]

We have CF 7.0.1 running with the latest driver (you have to see that you get your hands on a 3.4 driver package) and are using stored procedures successfully. Also we just finished hacking a stored procedure with return values. We are using Oracle Database 10.2.0.1.

[pdrich]

Carter,
Did it work for you to return to 10.1? We are trying to use Oracle 10.2 with CFMX7 and the Oracle thin driver. Queries work fine but stored procedures fail. I successfully ran the stored proc from SQL Plus using the same account, so it is not permissions-related. Data Direct drivers are not an option for us. We're at a loss and thinking maybe we need to return to 10.1.
Thanks
Pam