JDeveloper Throws Error "Invalid default value expression" for Complex Default Values for Date/Time Columns. (Doc ID 1367677.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle JDeveloper - Version: 11.1.1.0.0 and later   [Release: and later ]
Information in this document applies to any platform.

Symptoms

When dragging a table from a database user on to a class or database diagram following error is reported:
Error encountered:
[…] TESTDEFEXPR.TIME_CHANGED
COLUMN.TIME_CHANGED Data type DATE "cast(from_tz(cast(systimestamp as timestamp), to_char(systimestamp, 'tzh:tzm')) at time zone dbtimezone as date)"
Invalid default value expression: cast(from_tz(cast(systimestamp as timestamp), to_char(systimestamp, 'tzh:tzm')) at time zone dbtimezone as date)
Expected a DATE or TIMESTAMP expression

This error occurs when the table contains a column of type date/time and the column has a complex default value expression defined. An example of such table looks like:
CREATE TABLE testdefexpr ( id           NUMBER NOT NULL
                         , time_changed DATE   NOT NULL
);

ALTER TABLE testdefexpr MODIFY ( time_changed DEFAULT cast(from_tz(cast(systimestamp as timestamp), to_char(systimestamp, 'tzh:tzm')) at time zone dbtimezone as date) );


The error can also occur when creating an offline database object within JDeveloper and trying to add a complex expression to the default value. It works when using simple expressions like "SYSDATE" for the default value.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms