Friday, August 9, 2013

Logger 2.1.0 - 10g Sequence Fixed

In preparation for the release of Logger 2.1.0 I've decided to write a few posts highlighting some of the new features.

Apparently not too many people who use Logger are still running Oracle 11gR1 or lower since I was only notified of this issue now.  Patrick Jolliffe was kind enough to point out that there was an issue when installing Logger in a 10g. It turns out that  ":new.id := logger_logs_seq.nextval;" doesn't work in 10g. It only works in 11gR2 and above. Clearly it's been a while since I've used 10g, or better yet 11gR1.

The ability to reference sequences nextval in PL/SQL is mentioned in the 11gR2 New Features guide.

Logger 2.1.0 address this issue by only supporting the old "select into..." technique for various reasons. At first I was skeptical about performance impacts of having the context switch, however it appears that behind the scenes if called from PL/SQL it does an internal select into to obtain the value. Connor McDonald has some good slides about this here (see page 59 onwards).

Thanks to Dan McGhan and Scott Wesley for helping sort this issue out.

2 comments:

  1. you could try conditional compilation

    $IF DBMS_DB_VERSION.ver_le_11_1
    $THEN
    SELECT logger_logs_seq.NEXTVAL INTO :new.id FROM DUAL;
    $ELSE
    :new.id := logger_logs_seq.NEXTVAL;
    $END

    but this would limit to 10gR2 and higher

    ReplyDelete
    Replies
    1. Hi Sean,

      I did look at doing that but decided against it for this version since it really had no impact in the end (see Connor's article above). If the internal works of nextval has been changed in 12c then we'll look at changing it for the performance gain.

      Thanks,

      Martin

      Delete