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 …
Advertisement
Answer
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.