variable address to recieve the pointer to the query results.
qryFoliarHabits is my IN/OUT Generic Cursor Type.
<cfprocresult name="qryFoliarHabits"/>
Troy
Lincoln Milner wrote:
> I by no means am an expert in Oracle/CF storedproc calls, but my guess
> is that Oracle is looking for some kind of input for the IN/OUT
> parameter, and if you don't pass something in, then it yells.
>
>
> 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]
