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

8 Aug 2006 7:04 AM

Kent Liu

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!

--
Do or do not, there is no try.

8 Aug 2006 12:08 PM

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
john.opin***@sundesksoftware.com

Show quote

"Kent Liu" <Kent***@discussions.microsoft.com> wrote in message
news:F8716653-C1F0-4B92-8058-1DC70903527D@microsoft.com...
>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!
>
> --
> Do or do not, there is no try.

9 Aug 2006 12:42 AM

Kent Liu

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 ?

--
Do or do not, there is no try.

â€œJohn Opincarâ€ç¼–å†™ï¼š

Show quote

> 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
> john.opin***@sundesksoftware.com
>
> "Kent Liu" <Kent***@discussions.microsoft.com> wrote in message
> news:F8716653-C1F0-4B92-8058-1DC70903527D@microsoft.com...
> >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!
> >
> > --
> > Do or do not, there is no try.
>
>
>

9 Aug 2006 7:23 PM

Jim Heavey

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.

10 Aug 2006 1:41 AM

Kent Liu

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.

--
Do or do not, there is no try.

â€œJim Heaveyâ€ç¼–å†™ï¼š

Show quote

> 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.