RE: small issue with virtual columns and rebuild table

Thanks Paul.  I can reproduce that.

From: [email protected] [mailto:[email protected]] On Behalf Of Paul Dziemiela
Sent: Monday, March 12, 2012 5:23 AM
To: [email protected]
Subject: [toad] small issue with virtual columns and rebuild table


Hi folks,

Using TOAD 11.0.0.116.

If I create a table with virtual columns (11g feature) and run REBUILD TABLE, then the generated script attempts to insert values into the virtual columns failing with "ORA-54013: INSERT operation disallowed on virtual columns".

I am thinking the logic where the insert statement is constructed needs to be altered to skip over virtual columns.

Thanks!

Paul

Example:


1)      Create a table with a virtual column:

CREATE TABLE foo(
   real_column    NUMBER,
   virtual_column NUMBER GENERATED ALWAYS AS (real_column * 10)
);


2)      Insert some data


INSERT INTO FOO (real_column) VALUES (1);
INSERT INTO FOO (real_column) VALUES (2);
INSERT INTO FOO (real_column) VALUES (3);
COMMIT;


3)      Run REBUILD TABLE


4)      Run generated SQL script which is the following:

--  Copy the data from the renamed table
INSERT /*+ APPEND */
INTO PDZIEMIE.FOO INS_TBL
(REAL_COLUMN, VIRTUAL_COLUMN)
SELECT
REAL_COLUMN, VIRTUAL_COLUMN
FROM PDZIEMIE.FOO_X SEL_TBL ;

Commit ;


5)      ORA-54013: INSERT operation disallowed on virtual columns