Re: Oracle and ASP.NET - advice on where to look

From: <klimenkor_at_gmail.com>
Date: 10 Apr 2007 15:57:42 -0700
Message-ID: <1176245862.780249.92670@o5g2000hsb.googlegroups.com>

On Apr 10, 3:34 pm, sybra..._at_hccnet.nl wrote:
> On Tue, 10 Apr 2007 22:06:17 GMT, "Dereck L. Dietz"
>
> <diet..._at_ameritech.net> wrote:
> >I was asked at work to check on the use of Oracle stored procedures with
> >ASP.NET. My manager is under the impression that an Oracle stored procedure
> >cannot return a data set to an ASP.NET web application but asked me to check
> >to clarify this assumption.
>
> >I have no knowledge of ASP.NET and was wondering if anybody could direct me
> >in a direction where to look.
>
> >Thanks.
>
> I seem to recall there is a separate ASP or ODP section onhttp://otn.oracle.com, including samples.
> Generally speaking either this site, orhttp://asktom.oracle.comis a
> first resource, as it will provide immediate answers.
> I know for sure asktom has an article on how to call stored procedures
> from the everchanging plethora of Mickeysoft drivers.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

The origins of issue in MSSQL ability to return record sets as simple selects -

Begin
 .....
Select * from emp
End

On application side you create .NET request and receive recordset as a result returned from the function

In Oracle you can not return recordset as a function result. You have to define it as a parameter. To be more precise - as a REF CURSOR parameter.

CREATE OR REPLACE PACKAGE Types AS
  TYPE cursor_type IS REF CURSOR;
END Types;

CREATE OR REPLACE PROCEDURE GetEmp

	(in_deptno emp.deptno%TYPE,
	 out_recordset OUT Types.cursor_type) AS

BEGIN
  OPEN out_recordset FOR
    SELECT ename,

           empno,
           deptno

    FROM emp
    WHERE deptno = in_deptno
    ORDER BY ename;
END; So, can Oracle return data sets? Of course! Oracle can return data sets. Just does it slightly different way. I would also prefer to use Oracle ODP instead of MS OLEDB for Oracle. But both work just fine.

Hope it helps

Roman
Sr. DBA (Oracle/SQL) Received on Tue Apr 10 2007 - 17:57:42 CDT