07 November 2010

Answer to 5 November Quiz is Wrong (1621)

The 5 November quiz tested your knowledge of the way that the value of the SQL%ROWCOUNT attribute is set. Vitaliy wrote to me that while the quiz was scored correctly, the explanation of the results are wrong: 1. Until a SQL data manipulation statement is executed, SQL%ROWCOUNT yields NULL. Not zero. 2. SQL%ROWCOUNT=0 in your code because the COMMIT changes the SQL% attributes (reset rowcount to 0). (JFYI, in 7.3 commit does not reset SQL%ROWCOUNT to 0 and when upgrading some people had trouble with it). Vitaliy then points us to the Oracle documentation: "The value of the SQL%ROWCOUNT attribute refers to the most recently executed SQL statement from PL/SQL....The SQL%ROWCOUNT attribute is not related to the state of a transaction. ...Also, when an autonomous transaction is exited, SQL%ROWCOUNT is not restore to the original value in the parent transaction." I will change the text of the answer so that it is accurate. My apologies for this - but of course happy as always to be corrected! SF
Posted by at

3 comments:

  1. Sparky08 November, 2010 18:14

    OK. So if "when an autonomous transaction is exited, SQL%ROWCOUNT is not restore to the original value in the parent transaction.", why wouldn't the programmer expect the commit result from within the transaction to be visible outside the transaction? If I hadn't double checked my thinking in the documentation I would have had that correct. It would have been my first 100% correct week. :-(

    ReplyDelete
  2. iudith08 November, 2010 19:11

    Hello Sparky,
    In fact IT IS visible, exactly as the documentation says, but, since an autonomous transaction always ends with a COMMIT or ROLLBACK, what you see when exiting the autonomous transaction is always 0, because COMMIT and ROLLBACK always reset SQL%ROWCOUNT to 0.
    I agree with you that maybe the documentation does not underline this clearly enough.

    Best Regards,
    Iudith Mentzel

    ReplyDelete
  3. Sparky08 November, 2010 19:42

    Ah. Thanks.

    ReplyDelete

Subscribe to: Post Comments (Atom)