Problem with Oracle Thin JDBC driver

[alcoutim]

Hello all,

I'm facing the fowling problem:
We switch the default original (directdata drivers) drivers on the colfusion to the latest Oracle Thin JDBC driver 10.2.0.4
With the new drivers we start to get errors on some procedures calls
ex:

<cfif IsDefined("form.dsn")>
<cfset begin_date = '01/01/1900'>
<cfset end_date = '31/12/2199'>
<cfset oldlocale = SetLocale('English (US)')>

<cftry>
<cfstoredproc username="ARPS_APP" password="DEVBPASS" datasource="#form.dsn#"
procedure="auditres.arp_pkg_reporting.summary_audits_adjustments2">
<cfprocparam type="in" cfsqltype="CF_SQL_DATE" dbvarname="begin_date" value="#LSDateFormat(begin_date, 'dd/mm/yyyy')#">
<cfprocparam type="in" cfsqltype="CF_SQL_DATE" dbvarname="end_date" value="#LSDateFormat(end_date, 'dd/mm/yyyy')#">
<cfprocresult name="qView">
</cfstoredproc>
<cfdump var="#qView#">
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
</cfif>

I got the fowling error:
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SUMMARY_AUDITS_ADJUSTMENTS2' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

which is true because the Oracle procedure is:
PROCEDURE summary_audits_adjustments2(begin_date IN DATE, end_date IN DATE, p_cursor OUT cursor_type);
and I'm not given the 3rd parameter

So, how can this procedure as always run with the directdata drivers (the default cold fusion drivers)?
Is there any workaround to put the code running with the Oracle Thin JDBC driver?

Thank you in advance.

[paross1]

This is a known limitation.

Look under Usage in Livedocs for cfprocresult

CFML supports Oracle 8 and 9 Reference Cursor type, which passes a parameter by reference. Parameters that are passed this way can be allocated and deallocated from memory within the execution of one application. To use reference cursors in packages or stored procedures, use the cfprocresult tag. This causes the ColdFusion JDBC database driver to put Oracle reference cursors into a result set. ( You cannot use this method with Oracle's ThinClient JDBC drivers.)

Phil

[alcoutim]

Thank you a lot Phil

Now, I just want to ask you an opinion.
I've got a discussion going on here in my company between the application servers team and developers.
When you refer as an known limitation, you mean from the coldfusion side or from the Oracle Thin JDBC driver?

[paross1]

I'm not sure on what "side" the limitation lies. All that I know, if you want to use PL/SQL procedures that return result sets via ref cursors, you better be using ColdFusion Enterprise and NOT using the Oracle ThinClient drivers.

(Once upon a time, back when we were using ColdFusion 4.5 and Oracle 8.0, I was able to use ODBC drivers and still use ref cursors to return result sets, but I had to use the Oracle ODBC drivers and not those from Microsoft....but that was then and this is now, and noboy in his or her right mind would want to use ODBC.)

Phil

[alcoutim]

Thank you a lot for you comment Phil.

[paross1]

This might shed a little more light on things.

ColdFusion MX: Configuring the Oracle JDBC thin driver

ColdFusion Enterprise and J2EE Editions include DataDirect Technologies JDBC type IV driver for Oracle. This high-performance database connectivity component from the leading Oracle JDBC driver vendor supports the latest Oracle database enhancements and the full JDBC 3.0 specification, including pooling enhancements, BLOB/CLOB updates including read/write support, support for Multiple Open Results Sets, and savepoints. No special ColdFusion Server configuration is required to use this driver. Note: Oracle Ref Cursors are only supported with the DataDirect Technologies Oracle JDBC Driver.

Phil