This content has been marked as final. Show 5 replies
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.
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.
Thanks for the info Alex. I found an online article by Mark Williams about using a couple REF CURSORS to return multiple restsets:
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?
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; " +
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",
p1.Direction = ParameterDirection.Output;
//Select employees in department 20
OracleParameter p2 = cmd.Parameters.Add("refcursor2",
p2.Direction = ParameterDirection.Output;
//Select employees in department 30
OracleParameter p3 = cmd.Parameters.Add("refcursor3",
p3.Direction = ParameterDirection.Output;
//Execute batched statement
//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 =
OracleDataReader dr2 =
//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));