ORA-54013 Error

Today a developer boy ask me about the next error;

ORA-54013: INSERT operation disallowed on virtual columns

Of course, it sounds bad but it is very simple. He wanted insert value into a virtual column. I don’t like virtual columns, I prefers to calculate them by code app and not from database because of consume resources, but this are my two cents.

Please, let me show you the next testcase;

 
REM
REM Column TIME_STORED is a virtual column, derived from item_out minus item_in
REM

trialdb> CREATE TABLE TAB_WITH_VC
  2  (TIME_OF_OPERATION  TIMESTAMP WITH TIME ZONE,
  3   ITEM_IN DATE,
  4   ITEM_OUT DATE,
  5   TIME_STORED AS (ITEM_OUT - ITEM_IN)
  6  );
) 

Tabla creada.

REM
REM execute a describe of the table
REM 

trialdb> DESC TAB_WITH_VC
 Nombre                                    ¿Nulo?   Tipo
 ----------------------------------------- -------- ----------------------------
 TIME_OF_OPERATION                                  TIMESTAMP(6) WITH TIME ZONE
 ITEM_IN                                            DATE
 ITEM_OUT                                           DATE
 TIME_STORED                                        NUMBER

REM
REM Now try a insert into on all columns
REM 

Transcurrido: 00:00:00.03
trialdb>  INSERT INTO TAB_WITH_VC VALUES (SYSDATE, SYSDATE-3, SYSDATE+1, 0);
 INSERT INTO TAB_WITH_VC VALUES (SYSDATE, SYSDATE-3, SYSDATE+1, 0)
*
ERROR en línea 1:
ORA-54013: No se permite la operación INSERT en columnas virtuales

REM
REM We get the ora-54013 error. The solution as show below
REM 

trialdb> insert into TAB_WITH_VC (TIME_OF_OPERATION, ITEM_IN, ITEM_OUT)
  2  values (SYSDATE, SYSDATE-3, SYSDATE+1);

1 fila creada.

REM
REM Query the table, and get the calcualted value for TIME_STORED column
REM 

trialdb> select * from TAB_WITH_VC;

TIME_OF_OPERATION
___________________________________________________________________________
ITEM_IN              ITEM_OUT             TIME_STORED
____________________ ____________________ ___________
08/09/16 13:06:15,000000 +02:00
05/SEP/2016 13:06:15 09/SEP/2016 13:06:15           4

Oracle  and usually nor any other database engine stored physical data for virtual columns (other technologies name virtual columns like calculated columns).

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s