none
Updating dataTable Autoincrement columns with stored procedures and oracle RRS feed

  • Question

  • Hi,

    I've got an application with a strongly typed dataset wich contains several datatables related. Most of primary keys are autoincrement columns and I'm using oracle. So I need to synchronize these columns after an insert command to get children relation integrity. That's easy with swl server and @@identity, after the insert query I can concatenate a select query to get the table identity.

    That's not as easy in oracle, so sequences are need. What I'm doing is having a package integer variable in each table that I update in an insert trigger, then after each insert I can get the "identity". Another matter is that two statements (insert and select) cannot be concatenated in an OracleCommand commandText, so I have to use Stored Procedure and an output cursor parameter to get the select query result.

     

    Well, this was working really great in ADON.Net 1.1 but I've just updated the application to 2.0 framework and the OracleDataAdapter is not able to update the column inserted after calling the stored procedure, the autoincrement columns are not updated, the same with ADO.NET 1.1 is OK

    Friday, June 15, 2007 3:15 PM

All replies


  • Not sure why you're using a trigger - it isn't really necessary. In Oracle you can retrieve the next sequence number before an INSERT occurs. Just query nextval for the next sequence number.

    Code Snippet
    INSERT INTO suppliers
    (supplier_id, supplier_name)
    VALUES
    (supplier_seq.nextval, 'Kraft Foods');

     

    Friday, June 15, 2007 5:50 PM
  •  Paul P Clement IV wrote:


    Not sure why you're using a trigger - it isn't really necessary. In Oracle you can retrieve the next sequence number before an INSERT occurs. Just query nextval for the next sequence number.

    Code Snippet
    INSERT INTO suppliers
    (supplier_id, supplier_name)
    VALUES
    (supplier_seq.nextval, 'Kraft Foods');

     

    Of course using a trigger is not necessary but is a common way to update Identity fields in Oracle. Is the most similar to the SQL server behaviuor, you don't have to provide Identity values in your insert querys, as in SQL server, so ut's great if you are writing agnostics Data Source applications, the same query is good for Oracle an SQL server, so you only need to change the connection string, that can be store in a config file.

    Saturday, June 16, 2007 11:02 AM