FROM_TZ
FROM_TZ(x, time_zone) merges x and time_zone into one value.
It converts the TIMESTAMP x to the time zone specified by time_zone and returns a TIMESTAMP WITH TIMEZONE.
The time_zone must be specified as a string of the form +|- HH:MI.
SQL> SELECT FROM_TZ(TIMESTAMP '2012-07-18 08:15:12.1234', '-8:00') FROM dual;
FROM_TZ(TIMESTAMP'2012-07-1808:15:12.1234','-8:00')
---------------------------------------------------------------------------
18-JUL-12 08.15.12.123400000 AM -08:00
SQL>