Problems with Oracle SYS_CONTEXT function

Hey there,

  Today I've solved an interesting bug in the existent database procedures. Actually, it's not the bug right now, but it definitely will after a couple of years.

  We have an ASP.NET web site which works with the Oracle based database. There're no any entity model (it's old system with just a support for now) and all the queries are native SQL or calling SQL procedures from the database itself. Database generates new "unique" oid for every newly created entity with well-known procedure like this:

CREATE OR REPLACE FUNCTION new_uuid RETURN VARCHAR2 AS
  l_seed        BINARY_INTEGER;
  l_random_num  NUMBER(5);
  l_date        VARCHAR2(25);
  l_random      VARCHAR2(4);
  l_ip_address  VARCHAR2(12);
BEGIN
  l_seed       := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));
DBMS_RANDOM.initialize (val => l_seed);
  l_random_num := TRUNC(DBMS_RANDOM.value(low => 1, high => 65535));
DBMS_RANDOM.terminate;
  l_date       := conversion_api.to_hex(
    TO_NUMBER(
      TO_CHAR(SYSTIMESTAMP, 'FFSSMIHH24DDMMYYYY' )));
  l_random     := RPAD(
    conversion_api.to_hex(l_random_num), 4, '0');
  l_ip_address := conversion_api.to_hex(
    TO_NUMBER( REPLACE( NVL(
      SYS_CONTEXT( 'USERENV','IP_ADDRESS'), '123.123.123.123'), '.', '')));
RETURN SUBSTR(l_date, 1, 8)            || '-' ||
       SUBSTR(l_date, 9, 4)         || '-' ||
       SUBSTR(l_date, 13, 4)        || '-' ||
       RPAD(SUBSTR(l_date, 17), 4, '0') || '-' ||
       RPAD(l_random || l_ip_address, 12, '0');
END;

Original links: Universal Unique Identifier (UUID)

All the system and a web site works OK in the publish. But it crashes on every calling of new_uuid procedure on my local machine with the local http-server. Oracle system describes some error during conversion of the string value into number. And it definitely shows up the wrong line.

So, let's take a look at the SYS_CONTEXT function. At a first glance it looks OK. What it does? It's trying to get current IP address. And if we can't get an IP address we have to take some default one, like 123.123.123.123. Everything should be fine. But, we it's not.

What is wrong?

What do we know about the IP addresses? Many things, but one thing we should point out here is that IP address can be different. At least two types of IP addresses we have already now: IPv4 and IPv6. And the problem have many points of view:

1. Your PC or server may have both types of addresses.

2. IPv4 and IPv6 have different formats...and different splitters.

3. ...this one later in this article...

As you probably understand for now, SYS_CONTEXT procedure may return both of these types of IP. If you have IPv6 turned on your machine, you will definitely get the IPv6 address instead of your IPv4 one.

Solutions

Simplest one: Turn off IPv6 on your machine. And here is another one point of problems: all the world is going to use IPv6 in the recent years. Some giants like Google do it already now. All the IPv4 addresses is ended now and first and simplest solution is bad and it is only for FAST FIX.

Obvious one: you need to expect both of the addresses from the SYS_CONTEXT and appropriately work around that.