We are moving our Oracle Forms application to OCI (Oracle Cloud Infrastructure) which always runs in UTC time. Previously all our servers were in our local time zone, so we didn’t need to be concerned with time zone until now.
However, we cannot get it to work. According to the Oracle documentation, we set FORMS_DATETIME_SERVER_TZ=GMT (or leave blank because GMT is the default) and set the FORMS_DATETIME_LOCAL_TZ blank so that this picks up the users local time zone. (source: https://docs.oracle.com/middleware/12212/formsandreports/releasenotes-fnr/GUID-48A43867-FD34-4944-AB05-E34F3E600BDC.htm#FRREL5290). These values are set in the default.env files in the Forms Weblogic server.
When we set these values, I was expecting that CURRENT_DATE and LOCALTIMESTAMP would return the time in my local time zone, but both of these built-ins are returning the time in UTC time. This happens when we use these Oracle built-in inside a Forms Trigger (e.g. WHEN-NEW-FORM-INSTANCE), we always get UTC time. However, when I “select CURRENT_DATE into l_current_date from dual” I correctly get the date and time in my local time zone. It seems when I select the value from the database it works correctly, but using the CURRENT_DATE inside a Forms trigger, it reverts back to UTC time. I really want CURRENT_DATE to work the same way regardless of how a developer happens to reference it, otherwise this is very dangerous.
e.g.
:dummy.field1 := CURRENT_DATE; — returns the date and time in UTC time
select CURRENT_DATE into :dummy.field1 from dual; — returns the date and time in my local time zone
I know Oracle Forms is not commonly used, but surely others have managed to get this working, or am I missing something obvious?
We have been at this for a few days now, and even created the most basic ‘hello world’ app to remove any special config we may have had in our app, but even this simple app still shows CURRENT_DATE in UTC time. We have also tried forcing FORMS_DATETIME_LOCAL_TZ=Australia/Brisbane but this does appear to make any difference.
Thanks in advance.
Tim Daniell is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.