Data Model Editor Allows to Enter The Incorrect Data Type So That The Underlying SQL Fails With "ORA-01843: not a valid month" Error. (Doc ID 3049661.1)

Last updated on OCTOBER 13, 2024

Applies to:

Symptoms

The parameter definition on the data model editor has the following fields.
- Name
- Data Type
- Default Value
- Parameter Type

When "Date" is selected for Parameter Type, Data Type is automatically set to "Date" and it becomes read-only. However, it becomes editable once the other page is accessed then go back to the same parameter.
After that, the other value such as "String" can be entered to Data Type field. This combination can cause "ORA-01843: not a valid month" error when the data model is executed.

Also, the underlying data model file may have "xsd:string" even though Data Type shows "Date" on UI, to make it difficult to detect the cause of the error. Here is the example with several screenshot.

Here is the example how the error occurs.

1) Create a data model with the parameter "p_date". here is the SQL

2) Set the Parameter Type for the parameter "p_date" to Date. It automatically sets Date on the Data Type field and it becomes read-only.

3) Navigate to Data tab, select the "p_date" from the calendar then click View. It runs successfully to show the sample output.

4) Navigate to "p_date" parameter again. While it shows Date on both Parameter Type and Data Type, Data Type field is now editable, changed from step 2).

5) Data Type is (incorrectly) set to String and the data model is saved.

6) Navigate to Data tab, select the "p_date" from the calendar then click View. It fails with "ORA-01843: not a valid month" error.

7) Navigate to "p_date" parameter again. Data Type is shown as Date, even though it was changed to String at step 5.

8) Download the data model and open the xdmz file then _datamodel.xdm file. It shows datatype="xsd:string" that corresponds to the value at set at step 5.

At this point, the value on the data model file (xdmz > "_datamodel.xdm") and the one at UI are out of sync. It makes difficult to determine the cause of ORA-01843 error.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.