[prasad]

We are using utc.get_local_date function very frequently. Is there a better way to get the local date? This function call is taking a long time execute.

For example.

to_char(ORD.LATE_PICKUP_DATE,'DD-MON-YYYY') = '14-MAR-2008' (ORD is order release)

This condition in the where clause scans through all the rows in the order release ID.
How can I avoid the complete table scan?

Thanks,
Prasad.

[cunninghamg]

prasad

Its not the utc.get_local_date that is causing the full table scan - its the query.

If you look for a particular date in the Order_release table, it will scan all entries unless you add an index on the field being searched.

Adding further filtering criteria would help reduce the number of records to scan (if available).

Cheers
Gary

[chrisplough]

I definitely agree with Gary on this one. All queries in the system should be qualified with additional criteria (especially date criteria) in order to avoid full table scans. Many times the screens are modified, so that a date criteria is automatically includes, such as only returning results within the last 60 or 90 days.

--Chris

[prasad]

Gary & Chris.

Thank You for you hint. By narrowing the query, the performance increased significantly.

regards,
Prasad.

[sknmail@rediffmail.com]

Hi Chris,

We recently migrated our Devl environment from 4.5 to 5.5 CU#4 RU#1.

We could find when we run the the sql statement on migrated 5.5 Database
select UTC.GET_LOCAL_DATE (SYSDATE, NULL) from dual; throws an exception "ORA-20000: Undefined time zone for location".

However this same used to run on 4.5 Database. Now we have a bunch of reports which uses this function and all those reports are now throwing this exception. We do have raised a SR with Oracle on this, however still research is going on.

I will keep you update on this on receipt of a workaround.

Regards,
Suresh

[chrisplough]

Suresh,

Thank you. I haven't run into this issue myself, but will shop it around some of my contacts to see if I can help find a solution. Otherwise, I appreciate you keeping us updated.

--Chris

[cunninghamg]

Hi

So far as I recall the utc.get_local_date function is used to determine the timezone of the Location being passed in.
In you case - no location is being passed in - hence the error.

Did you expect this function to use your local PC timezone setting?
If so - I don't see this is possible.

Gary

[sprabhakar]

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