DDLIMP does not generate the correct SQL when there is a default value on Date type column. (Doc ID 1616010.1)

Last updated on JUNE 01, 2023

Applies to:

Symptoms

Using Siebel Tools Client, it is possible to set the default value on the Column object with the Physical Type "Date", "Date Time", or "UTC Date Time". In most cases this is the 'current time' but certain date such as '1900-01-01' may need to be set based on the particular business requirement.

When this configuration is reflected to the DDL statement for the database, it behaves differently in the following two scenario. For example, new stand alone table CX_TEST is defined with the UTC Date Time type column DATE_COL, with the default value '1900-01-01' as shown below.

1) Run Apply/DDL operation on the Siebel Tools Client.

Column DATE_COL is defined as below, reflecting the configuration on the Column object correctly.

DATE_COL                       date                 default '1900-01-01'

2) Run DDLDICT utility then DDLIMP utility to generate the SQL.

2-1) DDL file generated by DDLDICT holds the default value on the Column object correctly as below.

Column   8 = DATE_COL                         TIMESTAMP            DEFAULT '1900-01-01'

2-2) SQL file generated by DDLIMP does not have the quotation as below.

DATE_COL                       date                 default 1900-01-01

As a result, execution of the SQL file generated by DDLIMP (or execution of DDLIMP without /Q parameter) fails with the error. Depending on the database platform, error message may look like one of them.

<Oracle> ORA-00932: inconsistent datatypes: expected DATE got NUMBER

<DB2> SQL0104N An unexpected token "-" found following "default 1900". Expected tokens may include: "DEFAULT".

<SQL Server> DDL does not fail but the default value on DATE_COL may have strange value such as '1905-03-14'.

Scenario 2 may occur while the datebase upgrade, likely on upgphys phase to prevent the completion of the steps.

Cause

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