ODP.NET

I've got a question...

I want to call a function with a mixture of bind parms and non-bind parms and return a refcursor. For instance,

How would I call the following function with ODP: (BTW, I haven't compiled this so dont worry about the syntax. What I'm trying to accomplish is one round trip from the app server to the database that will return a query that is limited to the keys passed in as bind parms. Notice the join to the results table to restrict my production query to just those bind array values that are passed in after I insert them into my results table (index-organized table)). I get an error "SPECIFIED CAST IS NOT VALID" when I try to call this function with a mixture of bind parms (arrays) and non-bind parms. Can you do this? I do not see anything in the pdf file on the ODP documentation that says you cannot. but I'm thinking because you set the command.ArrayBindCount property to the number of things in your array, that this applies to ALL of the parms? Is that true? Any workarounds?

Thanx
Frank


CREATE OR REPLACE PACKAGE mypkg IS
TYPE RefCursor IS REF CURSOR;
FUNCTION myExport(keyid_in IN VARCHAR2
,where_clause_in IN VARCHAR2
,sessionid_in IN VARCHAR2
,key_nbr_in IN NUMBER
,key_flag_in IN CHAR
,key2_in IN NUMBER
,key2_flag_in IN CHAR) RETURN RefCursor;
END mypkg;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY mypkg IS
FUNCTION myExport(keyid_in IN VARCHAR2
,where_clause_in IN VARCHAR2
,sessionid_in IN VARCHAR2
,key_nbr_in IN NUMBER
,key_flag_in IN CHAR
,key2_nbr_in IN NUMBER
,key2_flag_in IN CHAR) RETURN RefCursor IS
myCur RefCursor;
BEGIN
-- This is not a bind parm, but one string value, not an array!
DELETE FROM session_results WHERE sessionid = keyid_in;

-- These are bind parms that will insert N records into my results table
INSERT INTO results VALUES(sessionid_in
,key_nbr_in
,key_flag_in
,key2_nbr_in
,key2_flag_in);
COMMIT;
OPEN myCur FOR
SELECT table1.key_nbr
,table1.key_flag
,table2.key2_nbr
,table2.key2_flag
FROM table1
,results
,table2
WHERE results.sessionid = keyid_in
AND table1.key_nbr = sresl.key_nbr
AND table1.key_flag = sresl.key_flag
AND table1.key_nbr = table2.key_nbr
AND table1.key_flag = table2.key_flag
and table2.key_nbr = results.key2_nbr
and table2.key_flag = results.key2_flag;


RETURN(myCur);
END myExport;
END mypkg;
/
SHOW ERRORS;