Recipe 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 SEQUENCE TBL0404_SEQUENCE
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 1.0E28
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER

The C# code is shown in Example 4-7.

Example 4-7. File: OracleSequenceValuesForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.OracleClient;

private const String STOREDPROCEDURENAME = "SP0404_INSERT";

// Stored procedure parameter name constants for table
private const String ID_PARM             = "pID";
private const String FIELD1_PARM         = "pField1";
private const String FIELD2_PARM         = "pField2";

//  . . . 

// Create the connection.
OracleConnection conn = new OracleConnection(
    ConfigurationSettings.AppSettings["Oracle_ConnectString"]);

// Create the command for the insert stored procedure.
OracleCommand cmd = new OracleCommand( );
cmd.Connection = conn;
cmd.CommandText = STOREDPROCEDURENAME;
cmd.CommandType = CommandType.StoredProcedure;
// Add the parameters and set values for them.
cmd.Parameters.Add(ID_PARM, OracleType.Int32).Direction =
    ParameterDirection.Output;
cmd.Parameters.Add(FIELD1_PARM, OracleType.NVarChar, 50);
cmd.Parameters.Add(FIELD2_PARM, OracleType.NVarChar, 50);
cmd.Parameters[FIELD1_PARM].Value = field1TextBox.Text;
cmd.Parameters[FIELD2_PARM].Value = field2TextBox.Text;

// Execute the insert query.
conn.Open( );
try
{
    cmd.ExecuteNonQuery( );
}
catch(Exception ex)
{
    MessageBox.Show(ex.Message, "Retrieving Oracle Sequence Values",
        MessageBoxButtons.OK, MessageBoxIcon.Error);
    return;
}
finally
{
    conn.Close( );
}

// Retrieve and display the sequence value.
int sequenceValue = (int)cmd.Parameters[ID_PARM].Value;
MessageBox.Show("Inserted record with ID = " + sequenceValue,
    "Retrieving Oracle Sequence Values", MessageBoxButtons.OK,
    MessageBoxIcon.Information);

Discussion

Oracle does not support auto-increment fields in the same way that SQL Server does. Instead, Oracle uses a sequence generator, which is a database object that is used to generate a sequence of unique values for a primary key column, but is not related to the table containing the column. As a result, a sequence generator can generate unique values for more than one table.

The SQL command CREATE SEQUENCE is used to create a new sequence as shown in the previous sample. The increment, start value, maximum value, cycling, and caching can be specified when creating the sequence.

Oracle stores the definition of sequences for a database in a single data dictionary table in the SYSTEM table namespace. As a result, all sequence definitions are always available.

A sequence is referenced in SQL statements using the NEXTVAL and CURRVAL keywords. NEXTVAL generates and returns the next sequence number while CURRVAL can be used to refer to that value as needed.

Oracle does not support batch queries to return data as SQL Server does. You can, however, return the sequence value by setting the return value of a stored procedure. The sample demonstrates using the NEXTVAL and CURRVAL keywords to generate the new sequence value when inserting a row using a stored procedure and subsequently setting the stored procedure's return value.