How-To Document

Based on Oracle Application Express (formerly called HTML DB) version 1.5

After completing this How-To, you should be able to:

• use date picker formats with forms and reports
• use date picker formats with forms and custom processes
• use the date picker with a custom date format
• validate dates in forms

Introduction

When using Oracle HTML DB to build applications, it is important to understand how to process dates rendered in different formats. This document describes how to process dates when building applications in Oracle HTML DB.

Software Requirements

• Oracle HTML DB
• Netscape 7.0 or Internet Explorer 5.5 or later Web browser

Create a Report and Form on the EMP Table

To demonstrate processing dates in Oracle HTML DB, you first need to create a report and form on the EMP table.

To create a report and form on the EMP table:

1. Navigate to the Application Builder.
2. Choose an application and click Go.
3. Click the Wizards tab.
4. Under Reports, click Report with links to form on a table (2 Pages).
5. In the Report With Links to Form on a Table Wizard:
   1. Choose the owner (this is same as the owner of your application) and enter EMP as the Table/View.
   2. Enter a page, a region title, a page title and choose a region template.
   3. Specify tab options, if you are not using an existing page.
   4. Choose all columns in the EMP table to display in the report.
   5. Continue to accept defaults until the summary page.
   6. Click Finish.

Changing the Date Format in a Report

If you run the report page you just created, it should look similar to the following figure.

Figure 1: Default Date Format in Report

Notice that the date is in the default format for this database, DD-MON-YY. If you would like to display dates in the MM/DD/YYYY format, you can add a format mask to this column in the report.

To add a format mask to the Hiredate column:

1. Navigate to the Page Definition of the page where you created the report on the EMP table.
2. Under Regions, click Q next to the name of the report region.
3. Click the edit icon next to HIREDATE.
4. In the Number / Date Format field, enter MM/DD/YYYY.
5. Click Apply Changes.

Now when you run the report page, the Hiredate column should look similar to the following figure.

Figure 2: Applying a Format Mask to a Column

Changing the Date Format in a Form (Using a Date Picker)

Now that the report is showing dates properly, you can change the format of the form item for Hiredate on the form page, by changing the Date Picker format to match the format used for the Hiredate column on the report page. To do this, you change the Display As of the form item for Hiredate.

To change the Hiredate form item to use Date Picker (DD/MM/YYYY):

1. Navigate to the Page Definition of the page where you created the form on the EMP table.
2. Under Items, click the item corresponding to the Hiredate column.
3. From Display As list, select Date Picker (DD/MM/YYYY).
4. Click Apply Changes.

Run the report page and click on an edit icon. Notice the error you receive, similar to figure 3.

Figure 3: Date Picker Error

The exception is raised because the date picker format is MM/DD/YYYY, yet the value retrieved from the database is in the default date format for this database, DD-MON-YY. Now, click Cancel and then Create on the report page to create a new entry. Enter information for a new employee and click Create. You should receive an error similar to figure 4.

Figure 4: Invalid Month Error

To correct these errors, edit the attributes of the Hiredate item and scroll down to the Source attributes. As shown in figure 5, the Source Type of the item is set to Database Column.

Figure 5: Hiredate Item Source

Figure 5 shows that the item called P6_HIREDATE is bound to the database column HIREDATE. The first error occurred because HIREDATE was retrieved from the database using the database default DD-MON-YY, but the item specified that it should use a date picker with the format MM/DD/YYYY.

The second error occurred because the process created by the wizard to insert data into the EMP table was expecting HIREDATE in the default format for this database, DD-MON-YY, but it was passed to the process in the format MM/DD/YYYY.

To fix both issues, you apply a format mask to the item. Enter MM/DD/YYYY in the Format Mask field as shown in Figure 6. The format mask should match the format of the Date Picker chosen for the item.

Figure 6: Add a Format Mask

When you run the application after adding a format mask you will be able to edit and create entries without error.

Handling Dates in a Custom Process

If you wrote your own process to insert rows into the EMP table instead of using a process created by the Report with Links to Form on a Table wizard, you would need to handle the date format for the value to be inserted into the HIREDATE column. For example, suppose your process was similar to the following.

BEGIN
                                    
    INSERT into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
                                    
    values (:P6_EMPNO, :P6_ENAME, :P6_JOB, :P6_MGR, :P6_HIREDATE, :P6_SAL, :P6_COMM, :P6_DEPTNO);

    COMMIT;

END;

Since the Date Picker used for the Hiredate item is in the format MM/DD/YYYY, you will need to apply the TO_DATE function to the value for the HIREDATE column, specifying the format of the date is MM/DD/YYYY as shown in the following.

BEGIN
                                    
    INSERT into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
                                    
    values (:P6_EMPNO, :P6_ENAME, :P6_JOB, :P6_MGR, TO_DATE(:P6_HIREDATE,'MM/DD/YYYY'), 
            :P6_SAL, :P6_COMM, :P6_DEPTNO);
                                    
    COMMIT;
                                    
END;

Using an Application Level Format Mask to Customize a Date Picker

If you need to represent dates in another format than those provided by the Date Picker item types, you have the option of using a custom date format with a Date Picker.

To use a custom date picker in an application:

• Create a Substitution String named PICK_DATE_FORMAT_MASK, giving it a Substitution Value of the format for the date.
• Change the Display As attribute of the date form item to Date Picker (use application format mask).
• Enter the custom date format as the format mask for the date item.
• Enter the custom date format as the format mask for the column on the report page.

The following procedures explains how to to change the date format in your application to YYYY-MM-DD.

To create a Substitution String called PICK_DATE_FORMAT_MASK with a value of YYYY-MM-DD:

1. Navigate to Application Builder.
2. Choose the application that has the form and report on the EMP table and click Go.
3. Click Edit Attributes.
4. Scroll down to Static Substitution Strings.
5. Under Static Substitution Strings, enter PICK_DATE_FORMAT_MASK in the Substitution String column and YYYY-MM-DD in the Substitution Value column. (See figure 7.)
6. Click Apply Changes.

Figure 7: Create PICK_DATE_FORMAT_MASK

To change the Hiredate item Display As and Format Mask:

1. Navigate to the Page Definition of the form page.
2. Under Items, click the name of item for Hiredate.
3. From Display As, select Date Picker (use application format mask).
4. Enter YYYY-MM-DD as the Format Mask under Source.
5. Click Apply Changes.

To change the format mask for the HIREDATE column on the report page:

1. Navigate to the Page Definition of the report page.
2. Under Regions, click Q next to the name of the report region.
3. Click the edit icon next to the HIREDATE column.
4. Enter YYYY-MM-DD as the Number / Date Format under Column Formatting.
5. Click Apply Changes.

After making the changes above, the report page and the form page should look similar to figures 8 and 9.

Figure 8: Hiredate Column YYYY-MM-DD

Figure 9: Hiredate Form Item YYYY-MM-DD

Validating Date Formats in a Form

Using a Date Picker item type in a form does not guarantee that the date format will be entered correctly. The user can choose not to use the picker and type directly in that field. If they enter an invalid month, 13 for example, they will receive an error from the database like the one seen above in figure 4. Specifically, they will receive "ORA-01843: Not a valid month" as the error message.

A better way to handle invalid user input is to validate it before it is passed to the process. If the format is invalid, the user is notified and returned to the form with a custom error message in-line.

To validate the user input for the Hiredate field, you add a validation to the form page. If you are running Oracle HTML DB in an Oracle 10 g database, you can use a Regular Expression to validate the user input. If the database is not 10 g you need to create a validation of type Function Returning Boolean. With the Function Returning Boolean method, you write a PL/SQL function to evaluate the input and return true or false depending on whether the date is in the proper format.

The following procedures describe how to create a Regular Expression validation and a Function Returning Boolean validation to validate that the value entered in the Hiredate field is in the format YYYY-MM-DD.

To create a Regular Expression validation on the Hiredate field: (10 g only)

1. Navigate to the Page Definition of the form page.
2. Under Validations, click Create.
3. In the Create Validation Wizard:
   1. Enter Validate Hiredate for the Name.
   2. Choose Inline with Field and in Notification from the Error Display Location list.
   3. Choose Regular Expression from the Validation Type list.
   4. Choose the item corresponding to the Hiredate field from the Validate Item popup list. (See figure 10.)
   5. As shown in Figure 10, enter the following in the Regular Expression field:

      ^[0-9]{4}-([0][0-9]|[1][012])-([012][0-9]|[3][01])$

   6. In the Error Message field, enter the following

      Date must be in the form YYYY-MM-DD.

   7. From the Condition Type list, select

      Request is Contained within Expression 1.

   8. In the Expression 1 field, enter the following:

      CREATE,SAVE

   9. Click Create.

Figure 10: Adding a Regular Expression Validation on Hiredate

To create a Function Returning Boolean validation on the Hiredate field:

1. Navigate to the Page Definition of the form page.
2. Under Validations, click Create.
3. In the Create Validation Wizard:
   1. Enter Validate Hiredate for the Name.
   2. From the Error Display Location list, select Inline with Field and in Notification.
   3. From the Validation Type list, select Function Returning Boolean.
   4. As shown in figure 11, enter the following code in the Validation field. Note that if your item is not named P6_HIREDATE, replace that part of the code with the name of the item corresponding to the Hiredate field.
      Copy

      Copied to Clipboard

      Error: Could not Copy

      declare
            l_date date;
          begin
            l_date := TO_DATE(:P6_HIREDATE,'YYYY-MM-DD');
            return true;
          exception when others then
            return false;
          end;

   5. In the Error Message field, enter the following: Date must be in the form YYYY-MM-DD.
   6. From the Condition Type list, select Request is Contained within Expression 1.
   7. In the Expression 1 field, enter the following: CREATE,SAVE
   8. Click Create.
   9. Under Validations, select Validate Hiredate.
   10. Under Error Messaging, choose the item corresponding to the Hiredate field from the Associated Item list.
   11. Click Apply Changes.

Figure 11: Adding a Function Returning Boolean Validation on Hiredate

If the user enters an invalid date format now, they are returned to the page with an in-line error message, similar to figure 12.

Figure 12: In-Line Validation Error for Hiredate