Verify Rails-style stored generated columns (Oracle 12c+ GENERATED ALWAYS AS ... STORED)

[yahonda]

Background

Oracle 12c introduced GENERATED columns:

-- Virtual (computed at read time)
ALTER TABLE products ADD full_name VARCHAR2(200) GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL;

-- Stored (persisted)
ALTER TABLE products ADD full_name VARCHAR2(200) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

oracle-enhanced supports the VIRTUAL form via the :as option since long ago (see OracleEnhanced::TableDefinition's :as option in valid_column_definition_options).

Rails 7.1+ standardised the generated: { type: :stored | :virtual, expression: "..." } keyword for add_column / change_column (see PostgreSQL's add_column_options! for the as: / stored: mapping). Oracle 12c+ supports both VIRTUAL and STORED, but oracle-enhanced has not been audited against Rails' standardised keyword.

Question to resolve

• Does add_column :t, :col, :string, as: "...", stored: true (or the newer generated: { ... } form) flow through to Oracle as GENERATED ALWAYS AS (...) STORED?
• Does the schema dumper round-trip the stored vs virtual distinction?
• Does change_column handle the same?

Verification steps

1. Read Rails::Generators::Migration migrations for the standardised generated-column DSL across Rails 7.0 / 7.1 / 7.2 / 8.0 / 8.1 / 8.2 — pin which keyword form is current
2. Probe oracle-enhanced against each form, observing emitted DDL
3. Where DDL is wrong / missing, decide whether to add a Rails 8.2-versioned MigrationCompatibility module (so older migrations keep their existing :as-only behavior)
4. Update schema_dumper to dump the correct form

Out of scope

• Identity columns — already supported (identity: true since Oracle 12.1+ and Phase 2: Auto-enable identity primary keys in Migration[8.2]+ (depends on Rails per-adapter-migration-compatibility) [DRAFT] #2596 wires the Migration[8.2] default)
• Auto-incremented sequences via trigger — already supported via primary_key_trigger:

Why now

Surfaced during the survey for #2702 / #2710. The VIRTUAL path predates STORED; STORED has been quietly supported by Oracle for over a decade. Worth a short audit to confirm AR's standardised DSL passes through correctly.

[yahonda]

Verification findings (Rails 8.2.0.alpha + Oracle Database 23ai 23.26 / 26ai Free)

Rails-side standardised DSL

The standardised generated-column DSL in activerecord 8.2.0.alpha is the flat keyword form, not the generated: { ... } hash form mentioned in the issue body:

• abstract/schema_statements.rb:1680 — valid_column_definition_options does not include :as / :stored. Each adapter adds them.
• PostgreSQL (postgresql/schema_definitions.rb:273), MySQL (mysql/schema_definitions.rb:99), and SQLite3 (sqlite3/schema_definitions.rb:41) all add :as, :type, :stored.
• Canonical column type is :virtual: t.virtual :col, type: :string, as: "...", stored: true.
• All three first-party dumpers emit as: / stored: (postgresql/schema_dumper.rb:106-107, mysql/schema_dumper.rb:18-19, sqlite3/schema_dumper.rb:45-46).

generated: { type: ..., expression: ... } does not appear anywhere in Rails 8.2.0.alpha — only the flat keywords.

Oracle-side syntax

Oracle 23ai accepts both STORED and MATERIALIZED as the trailing keyword on GENERATED ALWAYS AS (expr) columns; STORED is a synonym. DBMS_METADATA.GET_DDL round-trips them as MATERIALIZED, which is Oracle's canonical form. VIRTUAL has been supported since Oracle 11gR1; MATERIALIZED expression columns were introduced in Oracle 23ai.

Round-trip detection — the blocker

To round-trip stored: true through db:schema:dump, oracle-enhanced needs to detect at the column level whether a column is MATERIALIZED. Probed all candidate public dictionary views with the same example (T1 VIRTUAL, T2 MATERIALIZED, REG_DEF regular DEFAULT):

TABLE_NAME  COLUMN_NAME      VIRTUAL_COLUMN  DATA_DEFAULT             IN_*_TABLE_VIRTUAL_COLUMNS
T1          PRICE_WITH_TAX   YES             ROUND("PRICE"*1.2,2)     YES
T2          PRICE_WITH_TAX   NO              ROUND("PRICE"*1.2,2)     NO
(REG_DEF)                    NO              'reg'                    NO

• USER_TAB_COLS.VIRTUAL_COLUMN = 'YES' flags only VIRTUAL; MATERIALIZED is 'NO' and indistinguishable from a regular DEFAULT column.

• USER_TABLE_VIRTUAL_COLUMNS (and ALL_/DBA_ siblings) excludes MATERIALIZED. Source of the underlying internal view confirms why:

  -- INT$DBA_TABLE_VIRTUAL_COLUMNS
  from col$ vc, obj$ t, user$ tusr
  where bitand(vc.property, 8) = 8       -- bit 3 = VIRTUAL only
    and bitand(vc.property, 32) = 0

• A LEFT JOIN of user_tab_cols with user_table_virtual_columns does not help because MATERIALIZED has no row on the right.

• The distinguishing flag does exist in SYS.COL$.PROPERTY — VIRTUAL has bit 3 (8) and MATERIALIZED has bit 87 (2^87) — but SYS.COL$ requires SELECT_CATALOG_ROLE, which is not realistic to require from oracle-enhanced users.

• A heuristic ("VIRTUAL_COLUMN='NO' + DATA_DEFAULT references another column") is theoretically viable since regular DEFAULT cannot reference another column (Oracle rejects with ORA-00904 at CREATE), but parsing DATA_DEFAULT text in Ruby is brittle.

• DBMS_METADATA.GET_DDL round-trips MATERIALIZED correctly but parsing its CLOB output per table is heavy.

Side-effect break worth a separate issue

A column declared GENERATED ALWAYS AS (expr) MATERIALIZED is currently picked up by oracle-enhanced as a regular DEFAULT column (because virtual_column='NO' and data_default is populated), and is dumped to db/schema.rb as default: -> { 'ROUND("PRICE"*1.2,2)' }. Re-applying the dumped migration fails with ORA-00904 because Oracle does not allow regular DEFAULT to reference other columns. This is a pre-existing schema-dump round-trip break for MATERIALIZED tables, independent of this issue's stored: keyword question.

Decision

Hold implementation of stored: true / MATERIALIZED support in this issue until Oracle exposes a public catalog flag distinguishing MATERIALIZED columns (preferably a new column on *_TAB_COLS), or the project decides to require SELECT_CATALOG_ROLE / accept a DBMS_METADATA-based detection path. Implementing only the forward direction (stored: true -> DDL emit STORED) without a working round-trip would create a misleading partial feature.