Handy trick to avoid ORA-54013: INSERT operation disallowed on virtual columns

In some regards, virtual columns are great: Virtual columns don’t take up physical disk space, which allow for skinnier tables. However, an annoyance with virtual columns is Oracle will give you an ORA-54013 error if you try to insert a value into a virtual column.

Logically, this makes sense–you can’t specify a calculated value. But unfortunately, it means that any time you intend to insert into a table, you have to manually specify each of the columns, like this:

insert into some_table (column1, column2, column3, column4, column5, column6..

Now, in production code, it’s nearly always a good idea to manually specify each of the columns in an insert.

But occasionally, you might need to do ad-hoc inserts that are not part of the usual production flow. If you have an extremely wide table with hundreds of columns, this can be a great annoyance.

This annoyance can be circumvented using views! 🙂

Example of Encountering ORA-54013

Allow me to demonstrate: Suppose we have a table of financial transactions (notice the virtual column GRAND_TOTAL).

create table transactions
(
    transaction_id number constraint pk_transactions primary key
  , transaction_date date default sysdate
  , customer_first_name varchar2(80)
  , customer_last_name varchar2(80)
  , customer_email varchar2(255)
  , subtotal number not null
  , discounts number not null
  , taxes number not null
  , grand_total number generated always as (subtotal + discounts + taxes) virtual
);

exec dbms_random.initialize(1029384756);
 
insert /*+ append */ into transactions (
    transaction_id
  , transaction_date
  , customer_first_name
  , customer_last_name
  , customer_email
  , subtotal
  , discounts
  , taxes )
select rownum + 1000000
     , sysdate + dbms_random.value(-365, 365)
     , dbms_random.string('U', dbms_random.value(6, 10))
     , dbms_random.string('U', dbms_random.value(6, 15))
     , dbms_random.string('U', 8) || '@' || dbms_random.string('U', 6) || '.com'
     , dbms_random.value(0, 100)
     , dbms_random.value(-5, 0)
     , dbms_random.value(0, 100) * 0.09
from dual
connect by level <= 5000;

commit;

Suppose we wanted to delete a bunch of records from the TRANSACTIONS table, but we wanted to make a backup of the rows first.

SQL> create table backup_of_transactions
  2  as
  3  select *
  4  from transactions;
 
Table BACKUP_OF_TRANSACTIONS created.
 
SQL> delete from transactions;
 
5,000 rows deleted.
 
SQL> commit;
 
Commit complete.

Suddenly (for whatever reason), we need to put those rows back. Good thing we made a backup!

This is the annoying part though: We try to insert. But, if we don’t go through an specify each column manually, we encounter the ORA-54013 error.

SQL> insert into transactions
  2  select *
  3  from backup_of_transactions;
 
Error starting at line : 55 in command -
insert into transactions
select *
from backup_of_transactions
Error at Command Line : 55 Column : 1
Error report -
SQL Error: ORA-54013: INSERT operation disallowed on virtual columns
54013. 0000 -  "INSERT operation disallowed on virtual columns"
*Cause:    Attempted to insert values into a virtual column
*Action:   Re-issue the statment without providing values for a virtual column

Oracle makes us specify each column individually.

SQL> insert into transactions (transaction_id, transaction_date, customer_first_name, customer_last_name, customer_email, subtotal, discounts, taxes)
  2  select transaction_id, transaction_date, customer_first_name, customer_last_name, customer_email, subtotal, discounts, taxes from backup_of_transactions;
 
5,000 rows inserted.
 
SQL> commit;

Commit complete.

Another Solution

A handy trick is to make a view that excludes all virtual columns, and use this for import/export purposes.

SQL> create or replace view transactions_no_vcols
  2  as
  3  select transaction_id, transaction_date, customer_first_name, customer_last_name, customer_email, subtotal, discounts, taxes
  4  from transactions;
 
View TRANSACTIONS_NO_VCOLS created.

Now, instead of backing up the base table, we can make a backup of this view.

SQL> create table backup_of_transactions
  2    tablespace users_07
  3  as
  4  select *
  5  from transactions_no_vcols;
 
Table BACKUP_OF_TRANSACTIONS created.

Now, if we do our delete and commit…

SQL> delete from transactions;
 
5,000 rows deleted.
 
SQL> commit;
 
Commit complete.

…and suddenly find the need to restore our backup, we can insert into the view instead of the base table, making the insert easier since we don’t have to specify all the non-virtual columns.

SQL> insert into transactions_no_vcols
  2  select *
  3  from backup_of_transactions;
 
5,000 rows inserted.
 
SQL>
SQL> commit;
 
Commit complete.

Now you’re able to import and export data as needed!

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets