Why doesn't Oracle recognize a stored generated column definition?

user_120888 0
Viewed 170

I have the following generated column defined within a CREATE TABLE statement:

NET_ROWS_ADDED NUMBER(18) GENERATED ALWAYS AS (ROW_COUNT - PREV_ROW_COUNT) /*STORED*/,

ROW_COUNT and PREV_ROW_COUNT are simple NUMBER(18) columns previously defined in the same table.

Everything works fine as it's written.
But if I un-comment the STORED option, I get:

ORA-00907: missing right parenthesis

I need to convert this into a STORED generated column.
What is wrong with the syntax here? It all looks correct to me ...

calculated-columns syntax oracle sql
1 Answers

Oracle doesn't store a virtual column on disk, it is only evaluate on demand. You are probably confused with the STORED option in MySQL. Since there is no STORED clause in Oracle, it throws a syntax error.

From documentation:

GENERATED ALWAYS

The optional keywords GENERATED ALWAYS are provided for semantic clarity. They indicate that the column is not stored on disk, but is evaluated on demand.

VIRTUAL

The optional keyword VIRTUAL is provided for semantic clarity.

user_39896080
Related