How to get cursor as return value of oracle function back with ole

[Guest]

I am using System.Data.Oledb, I have oracle function that return the result
as REF Cursor:
CREATE OR REPLACE PACKAGE GET_EMPLOYEES AS
TYPE T_CURSOR IS REF CURSOR;
FUNCTION GetEmployees (userID_in IN VARCHAR2 := NULL) RETURN T_CURSOR;
.......

How to get the result back ?
I've tried:
Oledbconnection cn = new oledbconnection("provider=MSDAORA......
oledbdataapater da = new oledbdataapater("EMPLOYEES.GetEmployees", cn);
da.selectcommand.add("userID_in", oledbtype.varchar, 15).Value = dbnull.value;
....
da.fill(datatable)

but got error,
I known it could be done by using oracle procedure, but would anyone know
how to make this with System.Data.Oledb against oracle function ?

Thanks!

 

[John Opincar]

If possible, the simplest thing I can come up with is to wrap the function
in a stored procedure that has an in/out ref cursor parameter. ADO will
automaticaly use the in/out ref cursor as a recordset it returns.

John Opincar, MCAD.NET, SCJP
(e-mail address removed)

 

[Guest]

Thanks for your reply.
And yes, I could use the procedure to wrap the function of PL/SQL.

But I really want to know does if anyway to not using procedure and only
function to get the cursor back for PL/SQL in ado.net ?

 

[Guest]

Kent, I have been using Oracle for quites some time and I have never found a
way to successfully call a funtion, I always ended up calling a Procedure. I
could not say there is no way to do it, but I could not find a way.

 

[Guest]

Yes, Jim.
Using Oracle functions return int/varchar2 is OK, but not OK for cursor. I
think it should be some way to do this.

Wish the MVP known this way or Microsoft programmer could tell us how.