View Single Post
  #8 (permalink)  
Old June 6th, 2008, 14:55
sprabhakar sprabhakar is offline
Junior Member
 
Join Date: Dec 2006
Posts: 17
Thanks: 1
Thanked 2 Times in 2 Posts
Groans: 0
Groaned at 0 Times in 0 Posts
Rep Power: 0
sprabhakar is on a distinguished road
Send a message via AIM to sprabhakar
Smile Re: UTC.GET_LOCAL_DATE SQL Performance

The reason you are getting is error is that Oracle have modified the UTC package.
Prior to 55, the piece of code was coded as

if ( v_time_zone_gid is null) then
return local_date;
end if;

where local_date returned the system date from the database server. Whereas in 55 the above piece of code has been modified as

if ( v_time_zone_gid is null) then
raise_application_error(-20000, 'Undefined time zone for location'|| v_location_gid);
end if;

Thus making it mandatory to pass a location gid to get the local date from the server. A simple workaround to get this working without modifiying any of your reports would be to add a location with LOCATION_GID value as 'NULL' and the TIME_ZONE_GID with a value of your local timezone.

Alternatively, you can also use the below query to get local date in GMT.

select vpd.gmt_sysdate from dual

Please update if this works or I will suggest a few other options.

Regards,
Prabhakar
Reply With Quote