ORA-54013 error with Oracle virtual column
We're tring to migrate our PEGA database from DB2 to Oracle.
Due to performance issue, we've created table partitions based on some virtual columns.
But, after the virtual columns were created, we keep getting ORA-54013 error which says "INSERT operation disallowed on virtual columns".
After tracing the events, we found that the virtual column was included in the MERGE statement.
So, how to ignore these virtual columns when merging PEGA objects into database?
Thanks.
HCA Healthcare
US
This error happens because Oracle does not allow inserts or updates directly on virtual columns, and Pega’s generated MERGE SQL is trying to include those virtual columns in the insert/update list. To fix this, you should not create Pega rule or data columns as Oracle virtual columns directly on Pega-owned tables. Instead, keep only “real” columns on the Pega tables and move your virtual column logic into a view or into database indexes (for example, function-based indexes) so Pega never tries to write to them. If you must keep the virtual columns for partitioning, create a separate shadow table or view for reporting/partitioning and leave the original Pega table structure as delivered. After reverting/removing the virtual columns from the Pega base tables and using views/indexes for your performance needs, the MERGE statements will no longer try to touch virtual columns and the ORA-54013 errors will stop.