Getting a Sequence Value from Oracle

[ Team LiB ] 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.
Nội dung trích xuất từ tài liệu:
Getting a Sequence Value from Oracle[ Team LiB ]Recipe 4.4 Getting a Sequence Value from OracleProblemWhen you add a row into an Oracle table that uses a sequence to generate the value for aprimary key column, the value assigned to the column in the DataTable is replaced by avalue generated by the database. You need to retrieve the new value to keep theDataTable synchronized with the database.SolutionUse Oracles CURRVAL and NEXTVAL keywords.The sample code executes a stored procedure to insert a record into an Oracle table anduses the output parameter of the stored procedure to return the sequence value generatedfor 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_InsertCREATE PROCEDURE SP0404_INSERT( pID out number, pFIELD1 nvarchar2, pFIELD2 nvarchar2)asbegin 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_SequenceCREATE SEQUENCE TBL0404_SEQUENCE INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDERThe C# code is shown in Example 4-7.Example 4-7. File: OracleSequenceValuesForm.cs// Namespaces, variables, and constantsusing 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 tableprivate 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);DiscussionOracle 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 togenerate a sequence of unique values for a primary key column, but is not related to thetable containing the column. As a result, a sequence generator can generate unique valuesfor more than one table.The SQL command CREATE SEQUENCE is used to create a new sequence as shown inthe previous sample. The increment, start value, maximum value, cycling, and cachingcan be specified when creating the sequence.Oracle stores the definition of sequences for a database in a single data dictionary table inthe SYSTEM table namespace. As a result, all sequence definitions are always available.A sequence is referenced in SQL statements using the NEXTVAL and CURRVALkeywords. NEXTVAL generates and returns the next sequence number whileCURRVAL 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. Thesample demonstrates using the NEXTVAL and CURRVAL keywords to generate thenew sequence value when inserting a row using a stored procedure and subsequentlysetting the stored procedures return value.[ Team LiB ]