tailieunhanh - Getting a Sequence Value from Oracle

[ Team LiB ] Recipe 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. | Team LiB Recipe 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 pFIELDl nvarchar2 pFIELD2 nvarchar2 as begin INSERT INTO TBL0404 ID FIELD1 FIELD2 VALUES pFIELDl pFIELD2 SELECT 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 MINVALUE 1 NOCYCLE CACHE 20 NOORDER The C code is shown in Example 4-7. Example 4-7. File Namespaces variables and constants using System using using using using private const String STOREDPROCEDURENAME SP0404_INSERT Stored procedure parameter name constants for table private const String ID_PARM pID private const