Re: Does Oracle support non-US time zones?

From: David Fitzjarrell <oratune_at_aol.com>
Date: Fri, 12 Jan 2001 20:15:34 GMT
Message-ID: <93noks$65$1@nnrp1.deja.com>

Comments embedded.

In our last gripping episode the_maximus_at_my-deja.com wrote:
> Problem 1:
> The Oracle NEW_TIME() function supports only a limited number of time
> zones, mostly the ones for the US. The US (AST, EST, PST,...) time
> zones are documented in the Oracle manual. We may need other time
 zones
> for Paris/France, Beijing/China, Bombay/India, etc. I suspect that
> Internationalized versions of Oracle have support for these time
 zones.
> Most likely it is a part of internationalization/NLS support. Please
> let me know if you have any info about support of
 these 'international'
> time zones.
>

As you have found from the documentation the following timezones are the only ones listed as valid:

AST, ADT: Atlantic Standard or Daylight Time

BST. BDT: Bering Standard or Daylight Time

CST, CDT: Central Standard or Daylight Time

EST. EDT: Eastern Standard or Daylight Time

GMT: Greenwich Mean Time

HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.

MST, MDT: Mountain Standard or Daylight Time

NST: Newfoundland Standard Time

PST, PDT: Pacific Standard or Daylight Time

YST, YDT: Yukon Standard or Daylight Time

> Problem 2:
> Oracle sysdate gives us the current date but not the time zone. Is
> there any way in PL/SQL program to find out what is the time zone of
> the server? I can do a !date in sqlplus, but how do I do it in a
 stored
> procedure?
>
> Thanks in advance,
> Max
>
> Sent via Deja.com
> http://www.deja.com/
>

My first thought is to set up a library utilising one of the shared objects (*.so) in UNIX or a DLL in NT/2000 containing the necessary system call. You can then run an external procedure from within PL/SQL to fetch the value. An example, for NT, is shown below:

CREATE OR REPLACE LIBRARY nt_kernel
AS

   'c:\winnt\system32\kernel32.dll';
/

CREATE OR REPLACE PACKAGE disk_util
AS

   FUNCTION get_disk_free_space

     (root_path IN VARCHAR2,
      sectors_per_cluster OUT PLS_INTEGER,
      bytes_per_sector OUT PLS_INTEGER,
      number_of_free_clusters OUT PLS_INTEGER,
      total_number_of_clusters OUT PLS_INTEGER)

   RETURN PLS_INTEGER;
   PRAGMA RESTRICT_REFERENCES (get_disk_free_space,

      WNPS, RNPS, WNDS, RNDS);
END disk_util;
/

CREATE OR REPLACE PACKAGE BODY disk_util AS

   FUNCTION get_disk_free_space

     (root_path IN VARCHAR2,
      sectors_per_cluster OUT PLS_INTEGER,
      bytes_per_sector OUT PLS_INTEGER,
      number_of_free_clusters OUT pls_integer,
      total_number_of_clusters OUT PLS_INTEGER) RETURN PLS_INTEGER
   IS EXTERNAL
      LIBRARY nt_kernel          -- our library (defined previously)
      NAME "GetDiskFreeSpaceA"   -- the name of the function in
kernel32.dll
      LANGUAGE C                 -- external routine is written in C
      CALLING STANDARD PASCAL    -- uses the Pascal parameter convention
      PARAMETERS                 -- map PL/SQL to C parameters by
position
         (root_path STRING,
          sectors_per_cluster BY REFERENCE LONG,
          bytes_per_sector BY REFERENCE LONG,
          number_of_free_clusters BY REFERENCE LONG,
          total_number_of_clusters BY REFERENCE LONG,
          RETURN LONG);          -- a "return code" indicating success

or failure
END disk_util;
/

A PL/SQL script to make the call and report on the findings is shown below:

SET SERVEROUTPUT ON SIZE 100000
DECLARE
   lroot_path VARCHAR2(3) := 'C:\'; -- look at C drive    ldb_path VARCHAR2(3) := 'D:\'; -- look at D drive    lsectors_per_cluster PLS_INTEGER;
   lbytes_per_sector PLS_INTEGER;
   lnumber_of_free_clusters PLS_INTEGER;    ltotal_number_of_clusters PLS_INTEGER;    return_code PLS_INTEGER;
   free_meg REAL;
BEGIN
   /* Call the external procedure. We ignore the return code    || in this simple example.
   */
   return_code := disk_util.get_disk_free_space (lroot_path,

      lsectors_per_cluster, lbytes_per_sector,
      lnumber_of_free_clusters, ltotal_number_of_clusters);

   /* Using the drive statistics that are returned from the    || external procedure, compute the amount of free disk space.    || Remember Megabytes = (Bytes / 1024 / 1024)    */
   free_meg := lsectors_per_cluster * lbytes_per_sector *

               lnumber_of_free_clusters / 1024 / 1024;

   DBMS_OUTPUT.PUT_LINE('free disk space, megabytes, drive C: = ' || free_meg);

   /* Call the external procedure. We ignore the return code    || in this simple example.
   */
   return_code := disk_util.get_disk_free_space (ldb_path,

      lsectors_per_cluster, lbytes_per_sector,
      lnumber_of_free_clusters, ltotal_number_of_clusters);

   /* Using the drive statistics that are returned from the    || external procedure, compute the amount of free disk space.    || Remember Megabytes = (Bytes / 1024 / 1024)    */
   free_meg := lsectors_per_cluster * lbytes_per_sector *

               lnumber_of_free_clusters / 1024 / 1024;

   DBMS_OUTPUT.PUT_LINE('free disk space, megabytes, drive D: = ' || free_meg);

END;
/

I hope this helps.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/

Received on Fri Jan 12 2001 - 14:15:34 CST