Arun,
Thanks!

Yes this is similar to what I'm looking for, BUT there is a problem
here.
1. My requirement expects just 1 Time Offset value.

If I use your above query on TimeZone PDT , it would return regions
with different timeoffset .
(Its the same problem with LMT)

Is there any other function or means to get just one time offset value
for a given timezone ?? Any input is appreciated!

***************************
RESULT with Timezone PDT

SQL> set serveroutput on size 1000000
SQL> begin
2 for v_rec in (select tzname,tz_offset(tzname) v_offset from v
$timezone_names
3 where tzabbrev='PDT') loop
4 dbms_output.put_line('tzname '||v_rec.tzname||', offset '||
v_rec.v_offset);
5 end loop;
6 end;
7 /
tzname America/Dawson, offset -08:00
tzname America/Dawson_Creek, offset -07:00
tzname America/Ensenada, offset -08:00
tzname America/Inuvik, offset -07:00
tzname America/Juneau, offset -09:00
tzname America/Los_Angeles, offset -08:00
tzname America/Tijuana, offset -08:00
tzname America/Vancouver, offset -08:00
tzname America/Whitehorse, offset -08:00
tzname Canada/Pacific, offset -08:00
tzname Canada/Yukon, offset -08:00
tzname Mexico/BajaNorte, offset -08:00
tzname PST, offset -08:00
tzname PST8PDT, offset -08:00
tzname US/Pacific, offset -08:00
tzname US/Pacific-New, offset -08:00