Insert operation disallowed on virtual columns

Question

I dont understand the below issue where i am trying to insert the query and getting error as Insert operation disallowed on virtual columns. I took a export from production in sql developer and i tried to run those insert statements in test environment and in both the columns in tables in Production and Test enviornment has Virtual columns so normally there should not be issue while insert from production to test environment. But still i am getting the issue. Below is one of the insert statement. But there are thousands of insert statement. The ORD_DAYID column has virtual column.

Insert into TTRE (IS_GRP,ORD_SRCBK,ORD_MASTER_SRCBK,SALES_ORD_CHNL_SRCID,IS_CAPS,PROD_QTY_UNIT,PROD_U_PRICE,ORD_DAYID,ORD_SRCID,ORDLN_SRCID,ORDMAST_SRCID,ORD_LC_FLAG,PROD_SRCID,PARENT_PROD_SRCID,SYS_DB_NAME,SYS_LOAD_ID,SYS_LOAD_DTS,SYS_REC_SRC,CUST_SRCID,ENTITY_ID,ORD_DT,ORDMAST_DT,CURRENCY,IS_CONSUMPTION_INCLUDED,USER_CODE,WAREHOUSE_DIST_SRCID,IS_PASSED_BY,IS_CAN,PARENT_PROD_QTY,PROD_QTY,PARENT_PROD_U_PRICE,ORDLN_AMT,VAT_AMT,VAT_RATE_CODE,ORDLN_VAT_AMT,REBATE_ORD_AMT,GIFTCARD_ORD_AMT,ORDLN_PROMOTION,IS_INVOICED_FOR_STATISTIC,MAN_PROCESS_FLAG,ORDMAST_USER_CODE,IS_NPASS,ORIGIN,ORD_DTT,ITEM_CAT_CODE,REF_CAMPA_CODE) values (1,'43|14096967','43|14096898',6273,0,-1,0,20160330,13073855,59635337,416412266,-1,0,123076,'LM',358435,to_date('27-DEC-16','DD-MON-RR'),'NESSOFTLM',34118685,43,to_date('30-MAR-16','DD-MON-RR'),to_date('30-MAR-16','DD-MON-RR'),'COP',1,'NNCARDONMA1',4043,1,null,-1,-1,250000,-238095.2381,-11904.7619,'A',-250000,null,null,null,1,null,'NNCARDONMA1',0,29,to_date('30-MAR-16','DD-MON-RR'),null,null);

table schema is PPRD and error message is the same i have posted. When i run this insert statement i am getting error — Andrew
Use datapump (expdp & impdp) instead of inserts . It skips virtual (calculated) columns. — krokodilko
@Andrew I think OldProgrammer meant the DDL for the table rather than the schema it is in, and the whole error as it is reported, including the error code - presumably ORA-54013: INSERT operation disallowed on virtual columns. You can edit your question to add that information. — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2017-08-02T19:06:37

There doesn't seem to be any way to automatically exclude virtual columns from the generated insert scripts, which seems like a little bit of an oversight.

If you're stuck with your current script then you could edit that file to remove the column references and matching values, which is painful; or load the data into a temporary staging table and copy just the non-virtual columns across to the real table; or temporarily modify the target table in the test environment to rename the virtual column and replace it with a non-virtual one, which you can then discard, e.g. something like this:

alter table ttre rename column ord_dayid to ord_dayid_temp;
alter table ttre add ord_dayid number; -- match actual data type
@your_insert_script
alter table ttre drop column ord_dayid;
alter table ttre rename ord_dayid_temp to ord_dayid;

(being very careful to understand what you're doing and that you're changing/dropping the right things at the right time, and you have agreement from whoever owns the tables and database. Don't run code, especially destructive code, you find on the internet without being really really sure it's both suitable and correct...)

But that's also painful and error-prone, and be aware that the first alter after your inserts will implicitly commit. And you may not even have the privileges necessary to do it.

If you can regenerate the script then you can manually exclude the virtual columns. In the database export wizard, when you get to the 'Specify data' window:

enter your table name, click the lookup button which will list it in the top section, your table name, select it from that list, and click the down-arrow. That will move it to the bottom section of the screen, with the 'columns' entry showing as *. Click on that *, then on the pencil symbol that appears, which will give you a new dialog like this:

Click the plus sign to expand the list to show all of the columns, and untick the virtual one(s). Click OK then continue with your export. The generated script will only include the columns you left ticked.

It would be simpler, as @krokodilko suggested, to use datapump export and import, though it's possible you might need to involve your DBA if you don't have permission to do that. (And, of course, make sure you're actually allowed to copy data from production to test - depending on the kind of data involved that may not be a good idea.)

Insert operation disallowed on virtual columns

3 Answers