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.

You can only retrieve the return value of an Oracle function (as described in the previous section) by using the returns parameter, rather than using one of the ExecuteScalar methods to retrieve it.

Sequence

Oracle uses a sequence to generate a unique number instead of using the data type uniqueidentifier used by SQL Server. Either way, the primary purpose is to generate a series of unique numbers for the primary key column. Unlike the uniqueidentifier data type, a sequence is a database object that is not related to one or more tables that are used for the primary key value.

Oracle sequences are atomic objects and are consistent. That is, once you access a serial number, Oracle automatically increments the next number before processing the next request, ensuring that no duplicate values appear.

You can create an Oracle sequence using the Create SEQUENCE command. The parameters that are included with the command include increments, start values, maximum values, loops, and caches. You can access the sequence values using the Nextval and Currval keywords. Nextval returns the next number in the sequence, while Currval provides access to the current value. The sequence locations_seq in the HR schema is defined as follows:

CREATE SEQUENCE LOCATIONS_SEQ
　　INCREMENT BY 100
　　START WITH 1
　　MAXVALUE 9900
　　MINVALUE 1
　　NOCYCLE
　　NOCACHE
　　NOORDER

Most of the sequence code is self-explanatory. Nocycle indicates that the sequence will no longer generate additional values after the minimum or maximum value is reached. NoCache indicates that the sequence value will not be allocated until it is requested, and you can use the pre-allocation mechanism to improve performance. Noorder indicates that when numbering is generated, these numbers are not guaranteed to be returned in the order of the request number.