Returning a Recordset from an Oracle Stored Procedure to a .Net Caller

You wouldn't think this would be difficult, but every example I googled up was either incomplete, excessive, or needlessly confusing. So for my own future reference as much as anything, here it goes. . .

The minimalist implementation of a Stored Procedure that returns a recordset:

Oracle side:

CREATE OR REPLACE PROCEDURE RobCursorTestProc
(
  my_cursor IN OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN my_cursor FOR
    SELECT  TABLE_NAME,
            OWNER    
    FROM ALL_TABLES;
END;
SHOW ERRORS;

.Net Side

  using Oracle.DataAccess.Client; 

  OracleConnection OraConn = new OracleConnection();
  OraConn.ConnectionString = 
"Data Source=DBName;User Id=MyUser;Password=MyPW;";
  OraConn.Open(); 
  OracleCommand OraComm = new OracleCommand("RobCursorTestProc", OraConn);
  OraComm.CommandType = CommandType.StoredProcedure;
  OraComm.Parameters.Add
("Cursor", OracleDbType.RefCursor, ParameterDirection.Output ); 
  OracleDataReader OraDR = OraComm.ExecuteReader();
  if (OraDR.Read())
  {
    MessageBox.Show(OraDR.GetName(0));
    MessageBox.Show(OraDR.GetValue(0).ToString()); 
  }

Here's a bit more user friendly view:
http://snipplr.com/users/rengber/

File Under: Technology,