Using Ado.net to access Oracle 9i stored procedures (lower)

For the default installation of the HR architecture, the console output shows the fields (separated by semicolons) for each record in the two records of the employee 101:

101;9/21/1989 12:00:00 am;10/27/1993 12:00:00 AM; ac_account;110;

101;10/28/1993 12:00:00 am;3/15/1997 12:00:00 AM; ac_mgr;110;

The preceding code shows that the procedure in the package is specified using the package name (elect_job_history) and the name of the procedure (in this case, Getjobhistorybyemployeeid), separated by a period.

The code also explains how to define the REF CURSOR parameter of the result set. Note that the data type is oracletype.cursor and the direction is parameterdirection.output.

Also note that the connection remains open throughout the entire process of accessing the result set in REF CURSOR.

If the package returns more than one cursor, DataReader accesses the cursors in the order that you add them to the Parameters collection, rather than in the order in which they appear in the procedure. You can use the DataReader NextResult () method to advance to the next cursor.

A stored procedure that returns a single value

The Executeoraclescalar () method of the OracleCommand class is used to execute an SQL statement or stored procedure that returns a single value as a OracleType data type. If the command returns a result set, the method returns the value of the first column in the first row. If ref CURSOR is returned, rather than returning the value of the first row of the first column pointed to by ref CURSOR, the method returns a null reference. The ExecuteScalar () method of the OracleCommand class is similar to the Executeoraclescalar () method, except that it returns the value as a. NET Framework data type.

However, both methods are not used when using an Oracle stored procedure. An Oracle stored procedure cannot return a value as part of a returns statement, but only as an out parameter. For information, see the section on stored procedures that do not return data. Also, you cannot return a result set other than through REF CURSOR output parameters. This is discussed in the next section.