Why do you have an IN/OUT parameter defined if CF is only ever looking to get something out? My guess is that if you're only ever calling this to return something, then use an OUT parameter on the procedure, then you shouldn't run into trouble.
Or I may be way off here...
-----Original Message-----
From: Troy Simpson [mailto:[EMAIL PROTECTED]
Sent: Friday, February 13, 2004 6:51 PM
To: CF-Talk
Subject: Bug? Problem with Oracle Stored Procedure
All
I am using ColdFusionMX 6.1 with Oracle9i (9.2)
I have been creating and referencing Oracle Stored Procedures all day
long without a problem. And then this one of a kind procedure is giving
me a problem. It is different than the others that I have created and
used. This procedure only has one IN/OUT parameter which is a generic
cursor type. All the other procedures that I have created required at
least one numeric parameter and in IN/OUT generic Cursor parameter.
The following procedure is defined in an Oracle Package.
In the Package Specification:
-----------------------------------
-- Define a generic cursor type.
TYPE generic_curtype IS REF CURSOR;
/***********************************************************
*
**********************************************************/
PROCEDURE findAllFoliarHabits
(
p_FoliarHabitCur IN OUT Generic_CurType
);
-----------------------------------
In the Package Body:
/***********************************************************
*
**********************************************************/
PROCEDURE findAllFoliarHabits
(
p_FoliarHabitCur IN OUT Generic_CurType
)
AS
BEGIN
OPEN p_FoliarHabitCur FOR
SELECT *
FROM FOLIARHABIT;
END;
-------------------------------------------------
This is the function defined in my CFC:
<cffunction name="findAllFoliarHabits" access="public"
returntype="query" output="true" displayname="Find all foliar habits." >
<!--- --->
<cfstoredproc rocedure="#variables.schema#.Taxon_GW.findAllFoliarHabits"
datasource="#variables.dsn#">
<cfprocresult name="qryFoliarHabits"/>
</cfstoredproc>
<cfreturn qryFoliarHabits />
</cffunction>
--------------------------------------------------
This is the Error I get from CFMX:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'FINDALLFOLIARHABITS' ORA-06550: line 1, column 7: PL/SQL: Statement
ignored
********************************************
*** But why am I getting this Error? The procedure takes only one
argument which is a REF_CURSOR. So just for try, I add a bogus
<cfprocparam...> to my procudure call and it worked!!!
--------------------------------------------------
This is the function with the extra bogus <cfprocparam...>
<cffunction name="findAllFoliarHabits" access="public"
returntype="query" output="true" displayname="Find all foliar habits." >
<!--- --->
<cfstoredproc
procedure="#variables.schema#.Taxon_GW.findAllFoliarHabits"
datasource="#variables.dsn#">
<cfprocparam type="In" cfsqltype="CF_SQL_NUMERIC" dbvarname="p_TaxonID"
value="-1" />
<cfprocresult name="qryFoliarHabits"/>
</cfstoredproc>
<cfreturn qryFoliarHabits />
</cffunction>
*****************************************
*** Is this a bug or am I missing something?!?!?!? LOL
Thanks,
Tory
--
Troy Simpson
Applications Analyst/Programmer, OCPDBA, MCSE, SCSA
North Carolina State University Libraries
Campus Box 7111 | Raleigh | North Carolina
ph.919.515.3855 | fax.919.513.3330
E-mail: [EMAIL PROTECTED]
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
