DDLIMP does not generate the correct SQL when there is a default value on Date type column.
Last updated on JANUARY 21, 2014
Applies to:Siebel CRM - Version 7.8.2  and later
Information in this document applies to any platform.
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms