Accessing Oracle 9i Stored Procedures Using ADO.NET

  • Article

 

Bill Hamilton

April 2004

Applies to:

   Microsoft® ADO.NET
   Microsoft® SQL Server™
   Microsoft® .NET Oracle Provider
   Microsoft® .NET Framework version 1.1

Summary: Use the Microsoft .NET Oracle Provider in the Microsoft .NET Framework 1.1 to access Oracle stored procedures and functions. (24 printed pages)

Contents

Overview
Classes
Executing a Stored Procedure
Stored Procedures That Do Not Return Data
Accessing Return Values
Result Sets and REF CURSORS
Packages
Using a DataReader
Stored Procedures that Return a Single Value
Sequences
Filling a DataSet Using a DataAdapter
Updating Oracle with a DataAdapter
Working with Multiple Result Sets
Conclusion
Related Books

This article is a discussion of how to access Oracle stored procedures (named SQL programming blocks) and functions (which are programming blocks that return a single value) using ADO.NET.

You connect to an Oracle database using managed data providers, including the Microsoft® .NET Oracle Provider, the OLE DB .NET provider, the ODBC .NET data provider, or the ODP.NET provider from Oracle. This article uses the Microsoft® .NET Framework data provider for Oracle. Different functionality is available using the Oracle ODP.NET data provider or the Microsoft .NET Framework data provider for OLE DB.

The Oracle .NET data provider ships with the .NET Framework 1.1. If you are using .NET Framework 1.0, you will need to download the .NET Managed Provider for Oracle. For either version, the data provider classes are located in the System.Data.OracleClient namespace.

Overview

PL/SQL is the Oracle implementation of SQL. It is similar to T-SQL used by Microsoft® SQL Server™, with some exceptions that are discussed in more detail later in this article. Like T-SQL, PL/SQL extends standard SQL. PL/SQL is used to define named programming blocks such as stored procedures, functions, and triggers.

Classes

You use a subset of the classes in the System.Data.OracleClient namespace to execute Oracle stored procedures and functions. These classes are described in the following table:

Class Description
OracleCommand The SQL statement of stored procedure to execute against the Oracle database.
OracleConnection An open connection to the database.
OracleParameter A parameter to an OracleCommand and optionally, its mapping to a DataColumn.
OracleParameterCollection A collection of OracleParameter objects.
OracleType An enumeration of Oracle data types and structures.

Executing a Stored Procedure

Executing an Oracle stored procedure is similar to executing a SQL Server stored procedure. The following steps show how to execute an Oracle stored procedure and retrieve the result that it returns.

  1. Create a stored procedure called COUNT_JOB_HISTORY in the HR schema to count the records in the JOB_HISTORY table.

    CREATE OR REPLACE PROCEDURE COUNT_JOB_HISTORY
(
    reccount OUT NUMBER
)
IS
BEGIN
    SELECT COUNT(*) INTO reccount
    FROM JOB_HISTORY;
END COUNT_JOB_HISTORY;

    The HR schema is a sample that is included in the default Oracle installation.

  2. Add a reference to System.Data.OracleClient.dll—the Microsoft .NET Framework Data Provider for Oracle—to your project.

  3. Import the types in the OracleClient class with a using directive.

    using System.Data.OracleClient;

  4. Create an OracleConnection object.

    OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

    Substitute your values for the name of the Oracle database, the username, and the password.

  5. Create an OracleCommand object. Set its Connection property to the connection created in Step 4. Set its CommandText to the name of the stored procedure and its CommandText property to CommandType.StoredProcedure. The command object executes the specified stored procedure when you call one of the Execute() methods described in Step 8.

    OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;

    You might have to use escape sequences if your stored procedure name contains special characters. You can reuse an existing OracleCommand object by resetting the CommandText property.

  6. Create OracleParameter objects for the input, output, and return values and add them to the collection of parameters of the OracleCommand object.

    cmd.Parameters.Add("reccount", OracleType.Number).Direction =
    ParameterDirection.Output;

    This line of code is shorthand for the following two lines of code:

    cmd.Parameters.Add("reccount", OracleType.Number);
cmd.Parameters["reccount"].Direction = ParameterDirection.Output;

  7. If you are retrieving a result set, create a DataSet, DataTable, or DataReader. In this example, we are just getting the count in the output parameter created in Step 6.

  8. Open the connection and execute the stored procedure using one of the Execute methods of the OracleCommand object shown below:

    Method Description
    ExecuteReader Builds an OracleDataReader by executing a stored procedure that returns a result set.
    ExecuteNonQuery Executes a query or procedure that does not return a result set returning the number of rows affected.
    ExecuteOracleNonQuery Executes a query, returning the number of rows affected.

    This method also uses an OracleString parameter to return the row id for the last row modified by an UPDATE, INSERT, or DELETE query.
    ExecuteScalar Executes a query or procedure and returns either the return value or the value of the first column of the first row of the result set as a .NET Framework data type.
    ExecuteOracleScalar Executes a query or procedure and returns either the return value or the value of the first column of the first row of the result set as an OracleType data type.

    Remember to close the connection when you are done.

    conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

    If you are using a DataAdapter to fill a DataTable or DataSet, you can rely on the DataAdapter to open and close the connection.

  9. Process the results. In our example, the record count is available in the output parameter which is displayed to the console:

    Console.WriteLine(cmd.Parameters["reccount"].Value);

The code developed in this example to execute the stored procedure and retrieve the result follows:

OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("reccount", OracleType.Number).Direction =
    ParameterDirection.Output;

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Console.WriteLine(cmd.Parameters["reccount"].Value);

Stored Procedures That Do Not Return Data

The ExecuteOracleNonQuery() method of the OracleCommand class executes a SQL statement or stored procedure that does not return any rows. The method returns an int value representing the number of rows affected for the UPDATE, INSERT, and DELETE commands; otherwise it returns -1. If you are executing an INSERT, DELETE, or UPDATE statement that affects exactly one row, the method has a single OracleString out rowid argument that uniquely identifies the affected row in the Oracle database. You can use this value to optimize subsequent related queries.

You can also use the ExecuteNonQuery() method of the OracleCommand class to execute a stored procedure that does not return data, but you will not get the unique row identifier described above.

While neither of the above commands returns any data, output parameters and return values mapped to parameters are populated with data. This lets you return one or more scalar values from a stored procedure using either of the above commands.

The following Oracle stored procedure deletes all job history for an employee specified by the single input parameter and does not return any data.

CREATE OR REPLACE PROCEDURE DELETE_JOB_HISTORY
(
    p_employee_id NUMBER
)
IS
BEGIN
    DELETE FROM job_history
    WHERE employee_id = p_employee_id;
END DELETE_JOB_HISTORY;

The following code runs the stored procedure.

// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "COUNT_JOB_HISTORY";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameter specifying the employee for whom to delete records
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 102;

OracleString rowId;
// execute the stored procedure
conn.Open();
int rowsAffected = cmd.ExecuteNonQuery();
conn.Close();

Console.WriteLine("Rows affected: " + rowsAffected);

If you have not modified the default HR installation, the one record for employee 102 in the JOB_HISTORY table is deleted and the following is output to the console:

Rows affected: 1

Accessing Return Values

The RETURN statement immediately returns control to the calling program from a stored procedure. The RETURN statement in an Oracle stored procedure cannot return a value like it can in T-SQL.

Oracle functions are subprograms that compute and return a single value. They are structured similarly to stored procedures, except they always have a RETURN clause that must return a value.

A function that returns the e-mail for a specified employee follows:

CREATE OR REPLACE FUNCTION GET_EMPLOYEE_EMAIL (
    p_employee_id NUMBER
)
RETURN VARCHAR2
IS p_email VARCHAR2(25);
BEGIN
    SELECT EMAIL INTO p_email FROM EMPLOYEES
    WHERE EMPLOYEE_ID = p_employee_id;
    
    RETURN p_email;
END GET_EMPLOYEE_EMAIL;

You execute the function in the same way as a stored procedure. Use a ParameterDirection.ReturnValue parameter to get the result returned by the function. The following code shows how:

// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

// create the command for the function
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "GET_EMPLOYEE_EMAIL";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameters, including the return parameter to retrieve
// the return value
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("p_email", OracleType.VarChar, 25).Direction =
    ParameterDirection.ReturnValue;

// execute the function
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

// output the result
Console.WriteLine("Email address is: " + cmd.Parameters["p_email"].Value);

The console output shows the email address for employee 101.

Email address is: NKOCHHAR

Result Sets and REF CURSORS

You use the REF CURSOR data type to work with Oracle result sets. A REF CURSOR is a pointer into a result set returned by a PL/SQL query. Unlike a normal cursor, a REF CURSOR is a variable, a reference to a cursor that can be set to point to different result sets at execution time. Use a REF CURSOR output parameter to pass a result set from an Oracle structured program back to the calling application. Define an output parameter of data type OracleType.Cursor in the calling application to access the result set pointed to by the REF CURSOR. The OracleConnection must remain open while the REF CURSOR is being used.

Packages

One significant difference between stored procedures in PL/SQL and T-SQL is the Oracle package construct used by PL/SQL. There is no equivalent in T-SQL. A package is a container for logically related programming blocks such as stored procedures and functions. It has two parts:

  • Specification (or spec): Defines the name of the package and supplies method signatures (prototypes) for each stored procedure or function in the package. The header also defines any global declarations. The style of the spec is similar to a C or C++ header file.
  • Body: Contains code for the stored procedures and functions defined in the package header.

The parameters for each stored procedure or function appear within parentheses and are separated by commas. Each parameter is optionally tagged with one of three identifiers:

  • IN: The value is passed into the PL/SQL block from the calling application. IN is the default direction if one is not specified.
  • OUT: A value generated by the stored procedure and passed back to the calling application.
  • INOUT: A value that is passed into the PL/SQL block, possibly modified within the block, and returned to the calling application.

Each parameter is also tagged to indicate the data type.

The following package spec defines four procedures that create, retrieve, update, and delete data from the LOCATIONS table in the HR schema.

CREATE OR REPLACE PACKAGE CRUD_LOCATIONS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR);
PROCEDURE UpdateLocations (p_location_id IN NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR);
PROCEDURE DeleteLocations (p_location_id IN NUMBER);
PROCEDURE InsertLocations (p_location_id OUT NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR);
END CRUD_LOCATIONS;

The following excerpt from the package body for the above package spec shows the implementation details for the first procedure in the GetLocations package:

CREATE OR REPLACE PACKAGE BODY CRUD_LOCATIONS AS
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR)
IS
BEGIN
    OPEN cur_Locations FOR
    SELECT * FROM LOCATIONS;
END GetLocations;

-- Implementation of other procedures ommitted.

END CRUD_LOCATIONS;

Using a DataReader

You create an OracleDataReader by calling the ExecuteReader() method of the OracleCommand object. This section shows how to use a DataReader to access the result set returned by the stored procedure SELECT_JOB_HISTORY. The package spec follows:

CREATE OR REPLACE PACKAGE SELECT_JOB_HISTORY AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetJobHistoryByEmployeeId
(
    p_employee_id IN NUMBER,
    cur_JobHistory OUT T_CURSOR
);
END SELECT_JOB_HISTORY;

The package body defines a single procedure that retrieves a result set of the job history for a specified employee and returns it as a REF CURSOR output parameter:

CREATE OR REPLACE PACKAGE BODY SELECT_JOB_HISTORY AS
PROCEDURE GetJobHistoryByEmployeeId
(
    p_employee_id IN NUMBER,
    cur_JobHistory OUT T_CURSOR
)
IS
BEGIN
    OPEN cur_JobHistory FOR
    SELECT * FROM JOB_HISTORY
        WHERE employee_id = p_employee_id;

END GetJobHistoryByEmployeeId;
END SELECT_JOB_HISTORY;

The following code executes the procedure, creates a DataReader from the result set, and outputs the contents of the DataReader to the console.

// create connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction =
    ParameterDirection.Output;

// open the connection and create the DataReader
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();

// output the results and close the connection.
while(dr.Read())
{
    for(int i = 0; i < dr.FieldCount; i++)
        Console.Write(dr[i].ToString() + ";");
    Console.WriteLine();
}
conn.Close();

With the default installation of the HR schema, the console output shows the semi colon delimited fields of each of the two records for 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 code shows that a procedure within a package is specified using both the name of the package and the procedure, separated by a period—SELECT_JOB_HISTORY (GetJobHistoryByEmployeeId, in this case).

The code also shows how to define the REF CURSOR parameter for the result set. Notice that the data type is OracleType.Cursor and that the direction is ParameterDirection.Output.

Also note that the connection is kept open the entire time that the result set in the REF CURSOR is accessed.

If a package returns more than one cursor, the DataReader accesses them in the order you add them to the parameters collection, not the order in which they appear in the procedure. Use the NextResult() method of the DataReader to advance to the next cursor.

Stored Procedures That Return a Single Value

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

Having said that, neither of these methods is useful when working with Oracle stored procedures. Oracle stored procedures cannot return a value as part of the RETURN statement, only as OUT parameters—see the Stored Procedures That Do Not Return Data section. Also, you cannot return a result set except through a REF CURSOR output parameter—this is discussed in the next section.

You can retrieve the return value for an Oracle function only by using a RETURN parameter (shown in the previous section) and not by using the one of the ExecuteScalar methods.

Sequences

Oracle uses a sequence to generate unique numbers rather than the data type uniqueidentifier used by SQL Server. In either case, the main use is to generate a sequence of unique numbers for a primary key column. Unlike the uniqueidentifier data type, a sequence is a database object unrelated to the table or tables using it for primary key values.

An Oracle sequence is atomic and consistent. That is, once you access a sequence number, Oracle automatically increments the next number before processing the next request, guaranteeing no duplicates for a value.

You create an Oracle sequence using the CREATE SEQUENCE command. The command takes arguments including increment, start value, maximum value, cycling, and caching. You access sequence values using the NEXTVAL and CURRVAL keywords. NEXTVAL returns the next number in the sequence while CURRVAL provides access 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-describing. NOCYCLE means that the sequence will not generate more values after the minimum or maximum value is reached. NOCACHE means that sequence values are not allocated before they are requested; preallocation can be used to improve performance. NOORDER means that the numbers are generated without the guarantee that they will be returned in the same order they were requested.

The following code shows a stored procedure that requests a sequence value, uses it to set the primary key value when inserting a record into the LOCATIONS table, and returns the primary key value in an OUT parameter.

CREATE OR REPLACE PROCEDURE ADD_LOCATION (
    p_location_id OUT NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR
)
AS
BEGIN
    INSERT INTO LOCATIONS (
        LOCATION_ID,
        STREET_ADDRESS,
        POSTAL_CODE,
        CITY,
        STATE_PROVINCE,
        COUNTRY_ID)
    VALUES (
        LOCATIONS_SEQ.NEXTVAL,
        p_street_address,
        p_postal_code,
        p_city,
        p_state_province,
        p_country_id
    );
    
    SELECT LOCATIONS_SEQ.CURRVAL INTO p_location_id FROM DUAL;
END ADD_LOCATION;

The following code calls the stored procedure to insert a record and retrieve the returned sequence value.

// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "ADD_LOCATION";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameters for the stored procedure including the LOCATION_ID
// sequence value that is returned in the output parameter p_location_id
cmd.Parameters.Add("p_location_id", OracleType.Number).Direction =
    ParameterDirection.Output;
cmd.Parameters.Add("p_street_address", OracleType.VarChar).Value =
    "123 Any Street";
cmd.Parameters.Add("p_postal_code", OracleType.VarChar).Value = "33040";
cmd.Parameters.Add("p_city", OracleType.VarChar).Value = "Key West";
cmd.Parameters.Add("p_state_province", OracleType.VarChar).Value = "FL";
cmd.Parameters.Add("p_country_id", OracleType.VarChar).Value = "US";

// execute the command to add the records      
OracleString rowId;
conn.Open();
int rowsAffected = cmd.ExecuteOracleNonQuery(out rowId);
conn.Close();

// output the results
Console.WriteLine("Rows affected: " + rowsAffected);
Console.WriteLine("Location ID: " +
    cmd.Parameters["p_location_id"].Value);

The console shows that one record was inserted into the table along with the primary key value generated by the sequence.

Rows affected: 1
Location ID: 3300

Filling a DataSet Using a DataAdapter

You use a REF CURSOR to fill a DataSet using a DataAdapter. The following code uses the stored procedure GetJobHistoryByEmployeeId defined in the Using a DataReader section to populate a DataSet with the result set that it returns in a REF CURSOR output parameter.

The code to fill a DataSet using a DataAdapter follows:

// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction =
    ParameterDirection.Output;

// createt the DataAdapter from the command and use it to fill the
// DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

// output the results.
Console.WriteLine(ds.Tables[0].Rows.Count);

With the default installation of the HR schema, the output indicates that there are two JOB_HISTORY records for employee 101.

Updating Oracle with a DataAdapter

You cannot simply use the Update() method of the OracleDataAdapter when you fill a DataSet using a REF CURSOR parameter. This is because Oracle does not provide the information required to determine what the table and column names are when the stored procedure is executed. To use the Update() method of the DataAdapter, you have to create procedures to update, insert, and delete records from the underlying table. The approach is similar to the one used with SQL Server.

This section shows how to build a package that handles the create, retrieve, update, and delete operations required both to retrieve LOCATION data from the Oracle database and to update disconnected changes made to the DataSet data back to the Oracle database. The package header follows:

CREATE OR REPLACE PACKAGE CRUD_LOCATIONS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR);
PROCEDURE UpdateLocations (
    p_location_id IN NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR);
PROCEDURE DeleteLocations (p_location_id IN NUMBER);
PROCEDURE InsertLocations (
    p_location_id OUT NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR);
END CRUD_LOCATIONS;

The package body follows:

CREATE OR REPLACE PACKAGE BODY CRUD_LOCATIONS AS
-- retrieve all LOCATION records
PROCEDURE GetLocations (cur_Locations OUT T_CURSOR)
IS
BEGIN
    OPEN cur_Locations FOR
    SELECT * FROM LOCATIONS;
END GetLocations;

-- update a LOCATION record
PROCEDURE UpdateLocations (
    p_location_id IN NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR)
IS
BEGIN
    UPDATE LOCATIONS
    SET
        STREET_ADDRESS = p_street_address,
        POSTAL_CODE = p_postal_code,
        CITY = p_city,
        STATE_PROVINCE = p_state_province,
        COUNTRY_ID = p_country_id
    WHERE
        LOCATION_ID = p_location_id;        
END UpdateLocations;

-- delete a LOCATION record
PROCEDURE DeleteLocations (p_location_id IN NUMBER)
IS
BEGIN
     DELETE FROM LOCATIONS
     WHERE LOCATION_ID = p_location_id;
END DeleteLocations;

-- insert a LOCATION record
PROCEDURE InsertLocations
(
    p_location_id OUT NUMBER,
    p_street_address IN VARCHAR2,
    p_postal_code IN VARCHAR2,
    p_city IN VARCHAR2,
    p_state_province IN VARCHAR2,
    p_country_id IN CHAR
)
AS
BEGIN
    INSERT INTO LOCATIONS (
        LOCATION_ID,
        STREET_ADDRESS,
        POSTAL_CODE,
        CITY,
        STATE_PROVINCE,
        COUNTRY_ID)
    VALUES (
        LOCATIONS_SEQ.NEXTVAL,
        p_street_address,
        p_postal_code,
        p_city,
        p_state_province,
        p_country_id
    );
    
    SELECT LOCATIONS_SEQ.CURRVAL INTO p_location_id FROM DUAL;
END InsertLocations;
END CRUD_LOCATIONS;

The following code defines a DataAdapter to use the procedures defined in the above package to create, retrieve, update, and delete data allowing the DataAdapter. The DataAdapter can be used both to retrieve data into a DataSet and update the Oracle database with changes made to the DataSet.

// define the connection string
String connString = "Data Source=oracledb;User Id=UserID;Password=Password;";

// create the data adapter
OracleDataAdapter da = new OracleDataAdapter();

// define the select command for the data adapter
OracleCommand selectCommand =
    new OracleCommand("CRUD_LOCATIONS.GetLocations",
    new OracleConnection(connString));
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.Add("cur_Locations",
    OracleType.Cursor).Direction = ParameterDirection.Output;
da.SelectCommand = selectCommand;

// define the udpate command for the data adapter
OracleCommand updateCommand =
    new OracleCommand("CRUD_LOCATIONS.UpdateLocations",
    new OracleConnection(connString));
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.Parameters.Add("p_location_id", OracleType.Number, 4,
    "LOCATION_ID");
updateCommand.Parameters.Add("p_street_address", OracleType.VarChar, 40,
    "STREET_ADDRESS");
updateCommand.Parameters.Add("p_postal_code", OracleType.VarChar, 12,
    "POSTAL_CODE");
updateCommand.Parameters.Add("p_city", OracleType.VarChar, 30, "CITY");
updateCommand.Parameters.Add("p_state_province", OracleType.VarChar, 25,
    "STATE_PROVINCE");
updateCommand.Parameters.Add("p_country_id", OracleType.Char, 2,
    "COUNTRY_ID");
da.UpdateCommand = updateCommand;

// define the delete command for the data adapter
OracleCommand deleteCommand =
    new OracleCommand("CRUD_LOCATIONS.DeleteLocations",
    new OracleConnection(connString));
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Parameters.Add("p_location_id", OracleType.Number, 4,
    "LOCATION_ID");
da.DeleteCommand = deleteCommand;

OracleCommand insertCommand =
    new OracleCommand("CRUD_LOCATIONS.InsertLocations",
    new OracleConnection(connString));
insertCommand.CommandType = CommandType.StoredProcedure;
insertCommand.Parameters.Add("p_location_id", OracleType.Number, 4,
    "LOCATION_ID");
insertCommand.Parameters.Add("p_street_address", OracleType.VarChar, 40,
    "STREET_ADDRESS");
insertCommand.Parameters.Add("p_postal_code", OracleType.VarChar, 12,
    "POSTAL_CODE");
insertCommand.Parameters.Add("p_city", OracleType.VarChar, 30, "CITY");
insertCommand.Parameters.Add("p_state_province", OracleType.VarChar, 25,
    "STATE_PROVINCE");
insertCommand.Parameters.Add("p_country_id", OracleType.Char, 2,
    "COUNTRY_ID");
da.InsertCommand = insertCommand;

// define a DataTable and fill it using the data adapter
DataTable dt = new DataTable();
da.Fill(dt);

// ... do work that adds, edits, updates, or deletes records in the table

// call the Update() method of the data adapter to update the Oracle
// database with changes made to the data
da.Update(dt);

Working with Multiple Result Sets

Oracle does not support batch queries, so you cannot return multiple result sets from a command. With a stored procedure, returning multiple result sets is similar to returning a single result set; you have to use REF CURSOR output parameters. To return multiple result sets, use multiple REF CURSOR output parameters.

The package spec that returns two result sets—all EMPLOYEES and JOBS records—follows:

CREATE OR REPLACE PACKAGE SELECT_EMPLOYEES_JOBS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployeesAndJobs (
    cur_Employees OUT T_CURSOR,    
    cur_Jobs OUT T_CURSOR
);
END SELECT_EMPLOYEES_JOBS;

The package body follows:

CREATE OR REPLACE PACKAGE BODY SELECT_EMPLOYEES_JOBS AS
PROCEDURE GetEmployeesAndJobs
(
    cur_Employees OUT T_CURSOR,
    cur_Jobs OUT T_CURSOR
)
IS
BEGIN
    -- return all EMPLOYEES records
    OPEN cur_Employees FOR
    SELECT * FROM Employees;

    -- return all JOBS records
    OPEN cur_Jobs FOR
    SELECT * FROM Jobs;
END GetEmployeesAndJobs;
END SELECT_EMPLOYEES_JOBS;

The following code shows how to fill two related tables in a DataSet using the two result sets returned from the above package:

// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
    User Id=UserID;Password=Password;");

// define the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_EMPLOYEES_JOBS.GetEmployeesAndJobs";

// add the parameters including the two REF CURSOR types to retrieve
// the two result sets
cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =
    ParameterDirection.Output;
cmd.Parameters.Add("cur_Jobs", OracleType.Cursor).Direction =
    ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;

// create the DataAdapter and map tables
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "EMPLOYEES");
da.TableMappings.Add("Table1", "JOBS");

// create and fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);

// create a relation
ds.Relations.Add("EMPLOYEES_JOBS_RELATION",
   ds.Tables["JOBS"].Columns["JOB_ID"],
   ds.Tables["EMPLOYEES"].Columns["JOB_ID"]);

// output the second employee (zero-based array) and job title
// based on the relation
Console.WriteLine("Employee ID: " +
    ds.Tables["EMPLOYEES"].Rows[1]["EMPLOYEE_ID"] +
    "; Job Title: " +
    ds.Tables["EMPLOYEES"].Rows[1].GetParentRow(
    "EMPLOYEES_JOBS_RELATION")["JOB_TITLE"]);

The console output shows the job title for the second employee:

Employee ID: 101; Job Title: Administration Vice President

Conclusion

The Oracle .NET data provider makes it easy to execute stored procedures and access the return values, whether they are one or more scalar values or result sets. You can use Oracle procedures together with the OracleDataAdapter to fill a DataSet, work with the disconnected data, and update the changes to the Oracle database at a later time.

The main differences between Oracle procedures and Microsoft SQL Server stored procedures are that Oracle procedures must return values as output parameters, and that result sets must be returned as REF CURSOR objects to the calling program using output parameters.

ADO.NET Cookbook

ADO.NET in a Nutshell

Bill Hamilton is a software architect specializing in designing, developing, and implementing distributed applications using Microsoft .NET and J2EE technologies. An early technology adopter, he frequently evaluates, recommends, and helps his clients use new technologies effectively. Bill has written two books about ADO.NET.