Ok, so kind of a weird title, but let me explain. I believe that when you post to a forum, you are either seeking knowledge or sharing it. I was on the SQLDev forum a few days ago, and someone posted what can be perceived as an error when exporting a table.
Here is what he posted straight from the forum.
“I have a table with two virtual columns whose values are based on other columns on an Oracle database 11gR2. When I use the “Export Wizard” (Right click -> Export) on this table with the checked box “Export data”, the INSERT statements include theses 2 virtual columns which raises the following error “SQL Error: ORA-54013: INSERT operation disallowed on virtual columns.”
While the OP may have just been making a statement and not looking for an explanation, someone might actually wonder why this happens so here ya go:
This would make sense. When you do the export table, the data from the Virtual columns is actually included in the insert statement. Using the example from: ORACLE-BASE – Virtual Columns in Oracle Database 11g Release 1
CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, ‘JOHN’, ‘DOE’, 100, 5, 10);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, ‘JAYNE’, ‘DOE’, 200, 10, 20);
————————————————————————————————-
The INSERTS from the EXPORT gives you this (Partial Listing):
Insert into DBCAPOEIRA.EMPLOYEES (ID,FIRST_NAME,LAST_NAME,SALARY,COMM1,COMM2,SALARY1,SALARY2) values (1,’JOHN’,’DOE’,100,5,10,105,110);
Insert into DBCAPOEIRA.EMPLOYEES (ID,FIRST_NAME,LAST_NAME,SALARY,COMM1,COMM2,SALARY1,SALARY2) values (2,’JAYNE’,’DOE’,200,10,20,220,240);
————————————————————————————————–
The insert would attempt to put values in the last two columns, which are defined as VIRTUAL:
(1,’JOHN’,’DOE’,100,5,10,105,110);
id = 1
first_name=’JOHN’
last_name=’DOE’
salary=100
comm1=5
comm2=10
salary1=105 – This is a virtual column derived from the (ROUND(salary*(1+comm1/100),2)), calculation
salary2=110 – Likewise, this is also a virtual column GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL
The attempt to INSERT a value in those two columns will and should result in the error you are receiving.
While this confirmation of and explanation of why, was received well from the OP, what I didn’t expect was the unsolicited response from a “guru” who has obviously answered thousands of posts, all of which were right on point. Nor did I intend to have as much followup as I did from him. Hopefully now that “mr. guru” has voiced his opinion on the other issues with the Table Export feature in SQL Dev, the development team can take a look at it and see if some feature enhancement can be done. I’m sure there’s some Sun Tzu in there somewhere about using your enemy’s to get more for yourself.
While you may not agree, please help your fellow user community out in the forums and save your ranting for your own blog :). Hope this helps someone there that might be looking for why they are getting that error.
Like this:
Like Loading...
Related