-- These codes use an external c library to get the current GMT.
-- Similar c code can be created for both UNIX and Windows environment.


create or replace library C1ORA as '&ORALIB';
/
create or replace procedure get_gmt
(a1 OUT varchar2, a2 OUT varchar2, a3 OUT varchar2, a4 OUT varchar2)
AS
EXTERNAL
LIBRARY c1ora
NAME "get_GMT"
LANGUAGE c
Calling Standard c
PARAMETERS(a1 String, a2 String, a3 String, a4 String);
/
create or replace PROCEDURE        "UNIVERSALTIME"  (
UTC OUT date)
AS
  atime varchar2(30);
  aday  varchar2(30);
  amon  varchar2(30);
  ayear varchar2(30);
  ServerTime  date;
BEGIN
 get_gmt(atime, aday, amon, ayear);
 UTC := to_date(aday||'-'||amon||'-'||ayear||' '||atime,'dd-mon-yyyy hh24:mi:ss');
END UniversalTime;
/
PROMPT ------  Creating UNIVERSALTOSERVERTIME....
create or replace PROCEDURE        "UNIVERSALTOSERVERTIME"    (
TimeToConvert IN OUT date)
AS
  atime varchar2(30);
  aday  varchar2(30);
  amon  varchar2(30);
  ayear varchar2(30);
  ServerTime  date;
  OffsetHours number(2);
  UTC Date;
BEGIN
 get_gmt(atime, aday, amon, ayear);
-- UTC := to_date(SUBSTR(TRIM(aday),1,2)||'-'||TRIM(amon)||'-'||SUBSTR(TRIM(ayear),1,4)||' '||TRIM(atime),'dd-mon-yyyy hh24:mi:ss');
 UTC := to_date(aday||'-'||amon||'-'||ayear||' '||atime,'dd-mon-yyyy hh24:mi:ss');
 select ROUND((UTC - Sysdate)*24) into OffsetHours from dual;
 TimeToConvert := TimeToConvert - OffsetHours/24;
END UniversalToServerTime;
/