ADO.NET Cookbook

4.4. Getting a Sequence Value from Oracle

Problem

When you add a row into an Oracle table that uses a sequence to generate the value for a primary key column, the value assigned to the column in the DataTable is replaced by a value generated by the database. You need to retrieve the new value to keep the DataTable synchronized with the database.

Solution

Use Oracle’s CURRVAL and NEXTVAL keywords.

The sample code executes a stored procedure to insert a record into an Oracle table and uses the output parameter of the stored procedure to return the sequence value generated for the primary key column. The sequence value for the new record is displayed.

The sample uses a single stored procedure:

SP0404_INSERT

Used to add a new record into table TBL0404. The primary key field value is generated by the Oracle sequence TBL0404_SEQUENCE and is returned in the output parameter pID.

The sample uses one sequence:

TBL0404_SEQUENCE

Called by the stored procedure SP0404_INSERT to generate unique, sequential values for the primary key field ID in the table TBL0404.

The Oracle stored procedure is shown here in Example 4-5.

Example 4-5. Stored procedure: SP0404_Insert

CREATE PROCEDURE SP0404_INSERT
(
  pID out number,
  pFIELD1 nvarchar2,
  pFIELD2 nvarchar2
)
as

begin
  INSERT INTO TBL0404 (
    ID,
    FIELD1,
    FIELD2)
  VALUES (
    TBL0404_SEQUENCE.NEXTVAL,
    pFIELD1,
    pFIELD2
  );
    
  SELECT TBL0404_SEQUENCE.CURRVAL INTO pID FROM DUAL;
end;

The Oracle sequence is shown here in Example 4-6.

Example 4-6. Sequence: TBL0404_Sequence

CREATE ...