How can I create an Oracle VIRTUAL column?

Hi all,

I would like to create a virtual (computed) column in Oracle, like

col_name col_type GENERATED ALWAYS AS (function) VIRTUAL

I’ve tried several combinations of attributes for the <column> tag in <addColumn> element, but with no success. The best I got was a non-virtual column which would be prefilled with the function for existing records during deployment, but I didn’t manage to create a computed-only column.

Is there any solution?

Marcus.

You can just use a formatted sql file to add any sql statement that you want:

--liquibase formatted sql

--changeset bob:1
CREATE TABLE parts(
    part_id INT GENERATED ALWAYS AS IDENTITY,
    part_name VARCHAR2(50) NOT NULL,
    capacity INT NOT NULL,
    cost DEC(15,2) NOT NULL,
    list_price DEC(15,2) NOT NULL,
    gross_margin AS ((list_price - cost) / cost),
    PRIMARY KEY(part_id)
);

--rollback DROP TABLE parts purge;

You can use sql changelog as suggested above, or you can use xml / yaml and use the sql changetype.

You can also probably do this using addColumn changetype by combining it with modifySql. I have used that for example to add a partitioning clause when creating a table, but have not tried yet to use it for creating virtual columns.