ADO.NET 3.5 Cookbook, 2nd Edition

5.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 the CURRVAL keyword to return the sequence value in the output parameter of an Oracle stored procedure.

The solution 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 solution uses one table named OracleSequenceValue with the schema shown in Figure 5-5.

Figure 5-5. Schema for table OracleSequenceValue

The PL/SQL DDL to create the table OracleSequenceValue is shown in Example 5-7.

Example 5-7. DDL to create the table OracleSequenceValue

CREATE TABLE "ADODOTNET35COOKBOOK"."ORACLESEQUENCEVALUE" (
    "ID" NUMBER,
    "FIELD1" NVARCHAR2(50),
    "FIELD2" NVARCHAR2(50),
    PRIMARY KEY ("ID") VALIDATE )
TABLESPACE "ADODOTNET35COOKBOOK" PCTFREE 10 INITRANS 1 MAXTRANS 255
    STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING

The solution uses a single stored procedure that inserts a record into the table OracleSequenceValue and returns the sequence value generated by the ...