Examples for validating dates in plsql

For example, suppose your process was similar to the following.

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.

Specifically, they will receive "ORA-01843: Not a valid month" as the error message.

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.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.In practice, you may encounter other numeric types, such as FLOAT, INTEGER, and DECIMAL.These are subtypes of the four core numeric types in the preceding list.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.

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.

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.

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