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 ...
Get ADO.NET 3.5 Cookbook, 2nd Edition 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.