cfstoredproc with REF Cursor as out param

[qgh]

Hi all,

I am trying to call a stored procedure which has 3 input params and 1 out
apram which is a refcursor.
I am using CF MX 7

I am getting this error -
"PLS-00306: wrong number or types of arguments in call to 'GET_METADATA'
ORA-06550: line 1, column 7"

This is my CF Code for calling the Stored Procedure :-
I get the same error even if I use only OUT or or only CFPROCRESULT (and not
both)
===
<CFSTOREDPROC procedure = "METADATA.get_Metadata"
dataSource = "mydatasource"
returnCode = "No">

<CFPROCPARAM type = "IN"
CFSQLType = "CF_SQL_NUMERIC"
value = "pageid">

<CFPROCPARAM type = "IN"
CFSQLType = "CF_SQL_VARCHAR"
value = "myFormName">

<CFPROCPARAM type = "IN"
CFSQLType = "CF_SQL_VARCHAR"
value = "myfieldName">

<CFPROCPARAM type = "OUT"
CFSQLType = "CF_SQL_REFCURSOR"
variable = "METADATA_CURTYPE">
<CFPROCRESULT name = "metadataResult">
</CFSTOREDPROC>
========


Below is the PACKAGE definition :-
PACKAGE METADATA
IS

TYPE metadata_recordtype IS RECORD
(
fieldName FORMINPUTCONTROL.FIELDNAME%TYPE,
fieldValue DATA_FIELDVALUE.fieldvalue%TYPE
) ;

TYPE metadata_curtype IS REF CURSOR RETURN metadata_recordtype;


PROCEDURE get_metadata(
PAGEID IN Data_FieldValue.PageId%Type,
FORMNAME IN FORMCONTROL.FormName%Type,
INHERIT_FIELDNAME IN FormInputControl.fieldname%Type,
METADATA_CURVAR OUT metadata_curtype
);
END Metadata;


Please help !

Thanks in advance,
kn

[paross1]

You should not include the CFPROCPARAM tag for the OUT ref cursor, the result
set is captured in the CFPROCRESULT tag.

[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b17.htm#wp1102246[/url]

CFML supports Oracle 8 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

[qgh]

I still get the "PLS-00306: wrong number or types of arguments in call to
'GET_METADATA' ORA-06550: line 1, column 7"

I using only CFPROCRESULT below and not cfprocparam for the out variable
anymore
ANy ideas?

<CFSTOREDPROC procedure = "METADATA.get_Metadata"
dataSource = "mydatasource"
returnCode = "No">

<CFPROCPARAM type = "IN"
CFSQLType = "CF_SQL_NUMERIC"
value = "pageid">

<CFPROCPARAM type = "IN"
CFSQLType = "CF_SQL_VARCHAR"
value = "myFormName">

<CFPROCPARAM type = "IN"
CFSQLType = "CF_SQL_VARCHAR"
value = "myfieldName">


<CFPROCRESULT name = "metadataResult">
</CFSTOREDPROC>

[paross1]

What kind of drivers are you using? Live docs states "You cannot use this
method with Oracle's ThinClient JDBC drivers.", so if you are using ThinClient
JDBC drivers, you appear to be out of luck.

Phil