From: "Earl C. Ruby III" <eruby at switchmanagement.com>
Subject: Time zone package for Oracle
To: edi at agharta.de
Date: Fri, 8 Oct 2004 17:09:13 -0700
Organization: Switch Management
Reply-To: eruby at switchmanagement.com
User-Agent: KMail/1.6.2

Thank you for writing this excellent procedure! I am overhauling a large web 
application written in Perl that must deal with data coming in from many 
different time zones and then generate reports for people who live in many 
different time zones. Your routine simplifies things a great deal since Perl 
understands POSIX time zones but Oracle did not. Now getting Perl and Oracle 
to agree on the time is much, much simpler than before.

You asked for feedback on your time zone package for Oracle 
(http://www.weitz.de/timezone.html), so here goes...

I managed to get tz.conv working on Oracle 9.2.0.4 running on SuSE Linux 8.2 
Pro. I compiled the software just fine, ran install.sql with no errors, but 
then ran into some problems.

First off, I modified TARGET_DIR install the libtzconv.so library into a path 
other than $ORACLE_HOME/lib. (I used a path in the application's directory 
structure, not owned by Oracle.) That did not work. Moving the library to 
$ORACLE_HOME/lib fixed that problem. You might want to note that in your 
documentation.

The second, larger problem was that I wasn't set up to run external procedures 
from Oracle. I fixed that by adding the following branch to the LISTENER tree 
in listener.ora:

    (description =
      (address = (protocol = IPC)(key = tz))
    )

... and then adding the following SID_LIST_LISTENER to listener.ora:

SID_LIST_LISTENER=
  (sid_list =
    (sid_desc =
      (program = extproc)
      (sid_name = extproc_agent_tz)
      (oracle_home = /opt/u01/app/oracle/product/current_version)
    )
  )

... and finally adding this entry to tnsnames.ora:

extproc_connection_data =
  (description =
    (address_list =
      (address = (protocol = IPC)(key = tz))
    )
    (connect_data =
      (SID = extproc_agent_tz)
    )
  )

... and to show it works on an Oracle server that is configured to use UTC:

SQL> select to_char(tz.conv(sysdate, 'Etc/UTC', 'US/Pacific'), 'YYYY-MM-DD 
HH24:MI:SS') from dual;

TO_CHAR(TZ.CONV(SYS
-------------------
2004-10-08 16:29:44

If you want to add these notes on configuring RPCs in Oracle to your 
documenation.html file that would be fine by me. Thanks again for making this 
tool available.

-- 
Earl C. Ruby III <eruby at switchmanagement.com>
Senior Systems Engineer / Developer
Switch Management