3 Comments

  • Keep in mind with ODP.NET 10g that the following works in 9i's ODP.NET but is not working in 10g:



    to insert a row in a table with a sequence, you could batch the sequence query into the INSERT query:

    BEGIN INSERT INTO table (field1, field2) VALUES (sequence.NEXTVAL, :field2Value); SELECT sequence.CURRVAL INTO :newField1Value FROM DUAL; END;



    Executing this with an OracleCommand and the ExecuteNonQuery method would return the used sequence value in the output parameter newField1Value and would return the # rows affected: 1.



    With 10g, this is broken. ExecuteNonQuery only returns a value > 0 if the query is a clean INSERT, UPDATE or DELETE. As this is a batched query, the result will be -1. If your code relies on the return value (as it is defined in the interface!) to check whether the insert succeeded or failed, you have to re-write your code for 10g.

  • I am wondering why Oracale changed the ExecuteNonQuery behavior from 9.x to 10g release.
    My application is happily working with 9i ODP.NET. It is failing after moving to 10g, beucase I am checking for NumberOfRowsAffected and executing some logic based on this.
    I foind that ExecuteNonQuery return -1 only if the commandtype is Procedure. Where as if the CommandType is Text, it is retuning correct value (number of rows affected)

    Is it an issue with Oracle 10g? Or some issue got fixed and this is result of that fix? Any idea?

    Thank You,
    Venkat

  • You could also try this as:

    BEGIN
    SELECT sequence.nextval INTO :newField1Value FROM DUAL;
    INSERT INTO table (field1, field2) VALUES (:newField1Value, :field2Value);
    END;

    Maybe making the INSERT the last item in the anonymous PL/SQL block will help (I don't think so, though). Really, since in this example I'm securing the next sequence value first, the BEGIN..END isn't even necessary, and will thus work even if both statements are spread out between database sessions. Maybe that would work better.

Comments have been disabled for this content.