My Oracle Support Banner

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

To view full details, 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 a vibrant support community of peers and Oracle experts.