--Example 1
SELECT TZ_OFFSET('US/Eastern') AS tz_test
FROM dual;
 
--Example 2
SELECT TZ_OFFSET('-02:00') AS tz_test
FROM dual;
 
--Example 3
SELECT TZ_OFFSET(SESSIONTIMEZONE) AS tz_test
FROM dual;
 
--Example 4
SELECT TZ_OFFSET(DBTIMEZONE) AS tz_test
FROM dual;
 
--Example 5
SELECT
TZ_OFFSET(SESSIONTIMEZONE) AS tz_test,
SIGN(TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3))) * (
  TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 2, 2)) +
  TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 5, 2)) / 60
) AS tz_offset_num
FROM dual;
 
 
 
SELECT * FROM V$TIMEZONE_NAMES;