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.
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 sequenceTBL0404_SEQUENCE
and is returned in the output parameterpID
.
The sample uses one sequence:
TBL0404_SEQUENCE
Called by the stored procedure
SP0404_INSERT
to generate unique, sequential values for the primary key fieldID
in the tableTBL0404
.
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);
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.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.