Posts tagged virtual column
Database development in the Oracle Database is crucial for creating well balanced multi tier applications. This presentation describes a number of useful facilities and application architecture considerations around the database, taking into account some of the most recent insights.
The official slide deck from this presentation at Oracle Open World 2012:
The business rule states:
Only one entry is allowed per ID and per day and the time should be recorded.
The table involved (simplified for the blog post)SQL> create table test 2 (id number 3 ,inspection_dt date 4 );
Wouldn’t it be nice if it was possible to do it like this?SQL> create table test 2 (id number 3 ,inspection_dt date 4 ,constraint one_per_day unique (id, trunc (inspection_dt)) 5 ); ,constraint one_per_day unique (id, trunc (inspection_dt)) * ERROR at line 4: ORA-00904: : invalid identifier
This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry. As you can tell from the error message, this is not allowed. Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively.