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 20, 2019

Applies to:

Oracle JDeveloper - Version 11.1.1.0.0 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!


In this Document
Symptoms
Cause
Solution
References


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