SUBSTR "feature"

Hello fellow Oraclenites,

Came across an interesting problem with Oracle's SUBSTR function, Today. BTW, we are running Oracle 7.3.4 on Solaris 2.6. All of our reports being called from a OraTCL script was failing to run this week. Turns out the statement:

        SELECT SUBSTR(USERENV('SESSIONID'), -5) FROM DUAL; was returning NULL. We are extracting the last 5 digits of SESSIONID. It seems that somehow over the weekend either converting the database to UNICODE or something reset the SESSIONID sequence. We asked Oracle a while back how to reset it and they said it couldn't be done. But, I degress.

This caused the SESSIONID to be less than five digits. So we found out on a SUBSTR(str, m, n) if ABS(m) > LENGTH(str) and m < 0, SUBSTR returns NULL. I guess, we had assumed it would just return the string. It doesn't. Just curious, is this a feature or a bug? Couldn't find anything in the Oracle docs about this.

Thanks,
Steven
 --

                 _|_  |  _|_   "I am the way and the truth and the life.
                  | --|-- |     No one comes to the Father except through
Steven Whatley    |   |   |      me.  If you really knew me, you would
Houston, Texas        |           know my Father as well.  From now on,
swhatley_at_blkbox.com   |            you do know him and have seen him."
http://www.blkbox.com/~swhatley/        -- Jesus Christ (John 14:6-7 NIV)