Skip to main content
Participant
June 11, 2008
Question

Problem with Oracle Thin JDBC driver

  • June 11, 2008
  • 1 reply
  • 943 views
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.
This topic has been closed for replies.

1 reply

Participating Frequently
June 11, 2008
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
alcoutimAuthor
Participant
June 11, 2008
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?
Participating Frequently
June 11, 2008
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