Sync Virtual column works for TABLE , NOT for view

[jasonchenTJ]

Hi ora2pg team,

Currently, am testing the virtual column data sync from ORACLE 19.6 to PG 15.3.

Source DDL on oracle:

create table instalsched.TEST_TABLE (id int, name varchar2(20), vc_extra_payment_date as (CASE WHEN name='jason' THEN to_date('2024-03-01','YYYY-MM-DD') ELSE NULL END) ) ;

insert into instalsched.TEST_TABLE (id,name) values(1,'jason'); insert into instalsched.TEST_TABLE (id,name) values(2,'tom'); commit;

Target DDL on postgres:

create table instalsched.TEST_TABLE (id int, name text, vc_extra_payment_date date GENERATED ALWAYS AS (CASE WHEN name='jason' THEN date('2024-03-01') ELSE NULL END) STORED ) ;

when I try to sync the table directly , It works! (You don't need to add parameter EXCLUDE_COLUMNS to filter the virtual col)

When I warp a view base on table , it not works with error: view ddl: create view v_TEST_TABLE as select * from TEST_TABLE;

[postgres@whdcpsql004 plum]$ ora2pg -t COPY -c ./ora2pg_plum.conf [========================>] 0/0 tables (100.0%) end of scanning. Scanning view V_TEST_TABLE to export as table... DBD::Pg::db do failed: ERROR: column "vc_extra_payment_date" is a generated column DETAIL: Generated columns cannot be used in COPY. at /usr/local/share/perl5/Ora2Pg.pm line 15125. FATAL: ERROR: column "vc_extra_payment_date" is a generated column DETAIL: Generated columns cannot be used in COPY. Aborting export...

There is a workaround sync with view via add parameter EXCLUDE_COLUMNS V_TEST_TABLE(vc_extra_payment_date).

So why table and view are diff for sync virtual col ? ( VERSION is Ora2Pg v23.2)

Thanks Jason

[darold]

Hi Jason,

There is no way to know that the column of the view is a generated column in ALL_TAB_COLUMNS. If you know the way to know that please let me know, otherwise the solution is to use MODIFY_STRUCT as follow:

MODIFY_STRUCT    V_TEST_TABLE(id,name)

i-e specify all the columns except the virtual columns.

[jasonchenTJ]

Hi Darold,

Thanks for your explain! Your solution looks good!

MODIFY_STRUCT V_TEST_TABLE(id,name)

Thanks Jason