A script-enabled browser is required for this page to function properly.

About Format Elements for Dates

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.

Types of Date format elements

Oracle Forms uses the following Date format elements:

  1. User-created Date format elements for individual items
  2. PLSQL Date format element
  3. Builtin Date format element
  4. Database Date format element
  5. Input Date format element
  6. Output Date format element
  7. Error Date format element

1. Individual item's Date format element

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.

2. PLSQL Date format element

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.)

3. Builtin Date format element

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.)

4. Database Date format element

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.

Example for PLSQL, Builtin, and Database mask creation

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'''); 

5. Input Date format element

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.

6. Output Date format element

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.

7. Error Date format element

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.

Default values for the Input, Output, and Error masks

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] 

 


Built-in Date Format property

Format Mask property

PL/SQL Date Format property

User NLS Date Format property