5 Replies Latest reply on Sep 18, 2007 11:20 PM by Alex Keh - Product Manager-Oracle

    MARS with Enterprise Library

    587785
      Hi,
      How to get the result cursor from the Stored Procedure which is returing 3 cursors using Enterprise Library.
      Thanks in advance
        • 1. Re: MARS with Enterprise Library
          516073
          Does Oracle/ODP support MARS? I was under the impression that it doesn't but I could be mistaken.

          Does the Enterprise Library support ODP?

          Eric
          • 2. Re: MARS with Enterprise Library
            Alex Keh - Product Manager-Oracle
            Does Oracle/ODP support MARS?
            Yes, it does. But you've probably never seen any documentation on this. Why is that? Well, supporting multiple active result sets per connection (or even per command for that matter) has been part of Oracle for so long, we don't actually have a special term for MARS.
            Does the Enterprise Library support ODP?
            ODP.NET is a .NET Framework 2.0 provider. As per Microsoft's Enterprise Library DAAB Requirements, ODP.NET will work with DAAB.
            http://msdn2.microsoft.com/en-us/library/aa480458.aspx
            <excerpt>
            To run the Data Access Application Block, you need a database server running a database that is supported by a .NET Framework 2.0 data provider (in addition to the system requirements described on the Enterprise Library page). This includes SQL Server 2000 or later and Oracle 9i. The database server can also run a database that is supported by the .NET Framework 2.0 data providers for OLE DB or ODBC.
            </excerpt>
            • 3. Re: MARS with Enterprise Library
              516073
              Thanks for the info Alex. I found an online article by Mark Williams about using a couple REF CURSORS to return multiple restsets:

              http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_refcursors.html

              Would it be possible to give 2 SQL SELECT statements in the Command Text to get back 2 result sets (SQL Server can do this but I'm guessing maybe Oracle requires REF CURSORS)? What would be the syntax for that?

              Eric
              • 4. Re: MARS with Enterprise Library
                516073
                By the way, since I have your attention Alex, here's another "can Oracle do this" question: can ODP support Async queries? I really should know the answer to this but for some reason I've never tried it.

                Eric
                • 5. Re: MARS with Enterprise Library
                  Alex Keh - Product Manager-Oracle
                  To return two SELECT statement results from a single command text, you can use anonymous PL/SQL. Here's a code snippet below.

                  ODP.NET does not support asynch commands.

                                 string cmdtxt = "BEGIN " +
                                      "OPEN :1 for select ename, deptno from emp where deptno = 10; " +
                                      "OPEN :2 for select ename, deptno from emp where deptno = 20; " +
                                      "OPEN :3 for select ename, deptno from emp where deptno = 30; " +
                                      "END;";

                                 cmd.CommandText = cmdtxt;

                                 //ODP.NET has native Oracle data types, such as Oracle REF
                                 // Cursors, which can be mapped to .NET data types

                                 //Bind REF Cursor Parameters for each department
                                 //Select employees in department 10
                                 OracleParameter p1 = cmd.Parameters.Add("refcursor1",
                                      OracleDbType.RefCursor);
                                 p1.Direction = ParameterDirection.Output;

                                 //Select employees in department 20
                                 OracleParameter p2 = cmd.Parameters.Add("refcursor2",
                                      OracleDbType.RefCursor);
                                 p2.Direction = ParameterDirection.Output;

                                 //Select employees in department 30
                                 OracleParameter p3 = cmd.Parameters.Add("refcursor3",
                                      OracleDbType.RefCursor);
                                 p3.Direction = ParameterDirection.Output;

                                 //Execute batched statement
                                 cmd.ExecuteNonQuery();

                                 //Let's retrieve data from the 2nd and 3rd parameter without
                                 // having to fetch results from the first parameter
                                 //At the same time, we'll test MARS with Oracle
                                 OracleDataReader dr1 =
                                      ((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader();          
                                 OracleDataReader dr2 =
                                      ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader();
                                 
                                 //Let's retrieve both DataReaders at one time to test if
                                 // MARS works
                                 
                                 while (dr1.Read() && dr2.Read())
                                 {
                                      Console.WriteLine("Employee Name: " + dr1.GetString(0) + ", " +
                                           "Employee Dept:" + dr1.GetDecimal(1));
                                      Console.WriteLine("Employee Name: " + dr2.GetString(0) + ", " +
                                           "Employee Dept:" + dr2.GetDecimal(1));
                                      Console.WriteLine();
                                 }