Oracle Forms uses a format element to perform conversion operations on Dates in a variety of circumstances:
There are different format element for different Date conversion operations. Any format element you create yourself (via the Format_Mask property) is attached to one particular item. Such masks exist only if you specifically create them. The other Date format elements used by Oracle Forms are general, and are used for all Date items in a particular situation. These general-purpose masks always exist; however, you can control their settings.
The various conversion operations, and the format element Oracle Forms uses for each, are shown in the following two tables (Date to string, and string to Date). The masks are explained after the tables.
The settings of all of these Date format elements can be controlled. Some you can set via properties at design time. Others can be set via the runtime operating system's environment variables.
In general, it is advisable to do the following:
Operation Requiring Date-to-String Conversion |
Format Mask Used |
---|---|
Formatting operations: |
|
Displaying a value in an item that has a format element |
That item's format element |
Displaying a value in an item that does not have a format element |
Output |
Displaying a value in a Date LOV column |
Output |
Internal Forms operations: |
|
Getting :SYSTEM.CURRENT_DATETIME |
Builtin |
Getting :SYSTEM.EFFECTIVE_DATE |
Builtin |
Setting a CHAR return item from a Date LOV column |
Builtin (see note) |
Setting a CHAR item's initial value from $$DATE$$, $$DATETIME$$, $$DBDATE$$, or $$DBDATETIME$$ |
Builtin (see note) |
GET_ITEM_PROPERTY(item, DATABASE_VALUE) |
Builtin (see note) |
GET_ITEM_PROPERTY(item, ITEM_DEFAULT_VALUE) |
Builtin (see note) |
GET_ITEM_PROPERTY(item, RANGE_HIGH/LOW) |
Builtin (see note) |
GET_LIST_ELEMENT_VALUE(item, value) |
Builtin |
GET_PARAMETER_ATTR(List, key, paramtype, value)for a Date parameter defined at design time |
Builtin |
NAME_IN(variable) |
Builtin |
PL/SQL: |
|
TO_CHAR(value, format_mask) in PL/SQL |
That format element |
TO_CHAR(value) in PL/SQL |
PLSQL |
Implicit conversion in PL/SQL |
PLSQL |
Database SQL (includes database SQL embedded in PL/SQL): |
|
TO_CHAR(value, format element) in database SQL |
That format element |
TO_CHAR(value) in database SQL |
Database |
Implicit conversion in database SQL |
Database |
Storing a Date value returned by database SQL into a Forms CHAR variable |
Database |
Converting a Forms Date variable when it needs to be inserted into or compared against a CHAR column |
Database |
PL/SQL debugging: |
|
Displaying the value of an item or a parameter in the debugger |
Builtin |
Displaying the value of a local PL/SQL variable in the Debugger |
PLSQL |
Note:
If you set your form's Runtime_Compatibility_Mode property to 4.5, then this conversion operation uses the Output format element instead.
Operation Requiring String-to-Date Conversion |
Format Mask Used |
---|---|
Formatting operations: |
|
Extracting a value from an item that has a format element |
That item's format element |
Extracting a value from an item that does not have a format element |
Input |
Internal Forms operations: |
|
Setting :SYSTEM.EFFECTIVE_DATE |
Builtin |
Setting a DATE/DATETIME return item from a CHAR LOV column |
Builtin (see note) |
Setting a DATE/DATETIME item's initial value from a CHAR variable (using colon notation) |
Builtin (see note) |
ADD_LIST_ELEMENT(item, index, label, value) |
Builtin |
SET_PARAMETER_ATTR(List, key, paramtype, value) for a Date parameter defined at design time |
Builtin |
COPY(value, variable) |
Builtin |
DEFAULT_VALUE(value, variable) |
Builtin |
PL/SQL: |
|
TO_DATE(value, format_mask) |
That format element |
TO_DATE(value) in PL/SQL |
PLSQL |
Implicit conversion in PL/SQL |
PLSQL |
Database SQL (includes database SQL embedded in PL/SQL): |
|
TO_DATE(value, format_mask) in database SQL |
That format element |
TO_DATE(value) in database SQL |
Database |
Implicit conversion in database SQL |
Database |
Storing a CHAR value returned by database SQL into a Forms DATE variable |
Database |
Converting a Forms CHAR variable when it needs to be inserted into or compared against a Date column |
Database |
PL/SQL debugging: |
|
Setting the value of an item or a parameter in the debugger |
Builtin |
Setting the value of a local PL/SQL variable in the debugger |
PLSQL |
Note:
If you set your Form's Runtime_Compatibility_Mode property to 4.5, then this conversion operation uses the Input format element instead.
Oracle Forms uses the following Date format elements:
You create and set these masks through the Format_Mask property. As noted above, avoid creating these individual masks if the general-purpose masks will suffice.
You set this mask in the PL/SQL Date Format property . It is STRONGLY RECOMMENDED that for new applications, you set this property to a mask containing full century and time information. It is also recommended that you use the same setting for the Builtin Date format element. (See the example below.)
You set this mask in the Built-in Date Format property . It is STRONGLY RECOMMENDED that for new applications, you set this property to a mask containing full century and time information. It is also recommended that you use the same setting for the PLSQL Date format element. (See the example below.)
Each database session within a Forms application has a single Database Date format element. This mask is used by the database server to convert a string to a Date value, or vice versa, in the course of evaluating a query.
A default value for this mask is established by the Oracle Server's NLS_DATE_FORMAT (or NLS_LANG or NLS_TERRITORY) initialization parameter. This can be overridden in all new database sessions for a particular client, by setting the client's NLS_LANG and NLS_DATE_FORMAT environment variables.
Within a Forms application, the mask value can be further overridden on a session-by-session basis, by issuing an ALTER SESSION. It may simplify an application's logic if it is set to the same format element as the application's PLSQL_Date_Format and Builtin_Date_Format properties. (See the following example.)
Note that if you do an OPEN_FORM with the SESSION option specified, then the opened form will have a new database session; therefore, you might want to alter its session, as well.
To create and coordinate these masks, the PRE-FORM trigger in the application's initial form could contain:
SET_APPLICATION_PROPERTY(PLSQL_DATE_FORMAT, 'YYYY/MM/DD HH24:MI:SS');
SET_APPLICATION_PROPERTY(BUILTIN_DATE_FORMAT, 'YYYY/MM/DD HH24:MI:SS');
FORMS_DDL('ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY/MM/DD HH24:MI:SS''');
The Input Date format element (actually a set of masks) is used to extract a value from an item which has no format element. That is, the mask is used to convert a user-entered string into a native-format Date value.
The system administrator or end user may set the environment variables FORMS_USER_DATE_FORMAT and FORMS_USER_DATETIME_FORMAT to specify the format elements used to extract values from items of type DATE and DATETIME, respectively. In each case, multiple format elements may be specified, separated by vertical bars. When converting a user-entered string, the format elements are used in the order specified, until a conversion succeeds or until the format elements are exhausted.
For example, these runtime environment variables might be set as follows:
FORMS_USER_DATE_FORMAT FXFMDD-MM-RRRR
FORMS_USER_DATETIME_FORMAT FXFMDD-MM-RRRR HH24:MI:SS|FXFMDD-MM-RRRR HH24:MI
This would force the end user to enter values into DATE items (with no format element) in the format exemplified by 31-6-97. (The RRRR enables years between 1950 and 2049 to be entered with the century omitted). But for DATETIME items, the end user could enter values either in the format exemplified by 31-6-97 13:45:30 or else in the format exemplified by 31-6-97 13:45 (which would be interpreted as 31-6-97 13:45:00).
If either of these two runtime environment variables is not defined, then Oracle Forms will construct default format elements, as explained below.
The Output Date format element is used to display a value in an item which has no format element, or in an LOV column. There are actually two such masks, one for DATE items and LOV columns, and one for DATETIME items. (DATETIME LOV columns do not exist.). The system administrator or end user may set the environment variables FORMS_OUTPUT_DATE_FORMAT and FORMS_OUTPUT_DATETIME_FORMAT to specify these format elements explicitly.
If FORMS_OUTPUT_DATE_FORMAT is not defined, but FORMS_USER_DATE_FORMAT is defined, then the Output Date format element for DATE items is derived from FORMS_USER_DATE_FORMAT by taking the first format element (the value up to the first vertical bar) and stripping out all occurrences of FX and FM.
For example, if FORMS_OUTPUT_DATE_FORMAT is not defined and FORMS_USER_DATE_FORMAT is defined to be FXFMDD-MM-RRRR|FXFM-MON-RRRR, then the Output Date format element for DATE items is DD-MM-RRRR.
If neither FORMS_OUTPUT_DATE_FORMAT nor FORMS_USER_DATE_FORMAT is defined, then Oracle Forms will construct default format elements, as explained below.
There are similar rules for DATETIME items.
There is also an Error format element, which Oracle Forms uses in producing certain error messages that are issued when an end user enters an invalid value into a Date item. (There are actually two such masks -- one for DATE items and one for DATETIME items.)
For example:
FRM-50012: Date must be entered in a format like <error_Date_format>
FRM-50025: Date/time must be entered in a format like <error_Datetime_format>
The system administrator or end user may set the environment variables FORMS_ERROR_DATE_FORMAT and FORMS_ERROR_DATETIME_FORMAT to specify these format elements explicitly.
If FORMS_ERROR_DATE_FORMAT is not defined, but FORMS_USER_DATE_FORMAT is defined, then the Error Date format element for DATE items is simply taken to be the entire value FORMS_USER_DATE_FORMAT, including vertical bars.
If neither FORMS_ERROR_DATE_FORMAT nor FORMS_USER_DATE_FORMAT is defined, then Oracle Forms will construct default format elements, as explained below.
There are similar rules for DATETIME items.
If the runtime environment variables FORMS_USER_DATE_FORMAT and FORMS_USER_DATETIME_FORMAT are not both set, then the rules above do not specify all of the Input, Output, and Error format elements for DATE items and DATETIME items. It is recommended that these environment variables both be set. That will avoid having the complex rules below come into play.
In general, Oracle Forms assigns default values that provide compatibility with Release 6.0 and earlier. The behavior in those releases may actually differ slightly in some cases, but in such cases the behavior in prior releases is considered to be erroneous.
The following rules distinguish between DATE2 and DATE4 items (see Built-in Date Format property for details), with the additional proviso that an LOV column whose datatype is DATE is considered to be a DATE2 object. Note that there are no DATETIME LOV columns, and LOV columns do not directly participate in builtins.
Oracle Forms starts with the format element derived from the current NLS environment. This may be specified explicitly by setting the environment variable NLS_DATE_FORMAT. Otherwise, NLS will compute a default format element based on the current language. Call this NLS format element "<YY_mask>".
Next, Oracle Forms computes a second format element (call it "<YYYY_mask>") from <YY_mask> as follows: If <YY_mask> contains "YY" but not "YYYY", then <YYYY_mask> is set to <YY_mask>, but with the "YY" replaced by "YYYY". Similarly, "yy", "RR", or "rr" will be replaced by "yyyy", "RRRR", or "rrrr" in <YYYY_mask>. Otherwise, <YYYY_mask> is set to <YY_mask>.
Given the above, the remaining unspecified Input format elements for DATE2, DATE4, and DATETIME objects become:
DATE2: FXFM<YY_mask>
DATE4: FXFM<YYYY_mask>
DATETIME: FXFM<YYYY_mask> HH24:MI:SS
FXFM<YYYY_mask> HH24:MI
The leading FXFM is omitted if the format element already contains an FX or FM.
Next, any occurrences of FX and FM are removed from <YY_mask> and <YYYY_mask>.
Given the above, the remaining unspecified Output format elements become:
DATE2: <YY_mask>
DATE4: <YYYY_mask>
DATETIME: <YYYY_mask> HH24:MI:SS
Finally, for the remaining unspecified Error format elements, the first occurrence (if any) of RR, rr, RRRR, or rrrr in <YY_mask> and <YYYY_mask> is replaced by the corresponding number of Y’s or y’s.
Given the above, the remaining unspecified Error format elements become:
DATE2: <YY_mask>
DATE4: <YYYY_mask>
DATETIME: <YYYY_mask> HH24:MI[:SS]