Add a Virtual Column to table with XMLType column Oracle 12c

Question

I have the need to create a new table to store the incoming XML message and am planning to add some virtual columns in the create table statement itself. This works great! I have been searching and could not find a way to alter this table to add a new Virtual Column. Searching on google let me to this page

But that is from 2007 and I wonder if this functionality is now available in Oracle 12c. Anyone tried this before and have any suggestions? Thanks!

Ex:

CREATE TABLE Import_Log (message_guid varchar2(36),
                 xml_data XMLType)
  XMLTYPE xml_data STORE AS BINARY XML
  VIRTUAL COLUMNS
  (policynumber AS (XMLCast(XMLQuery('/PolicyMessage/Policy/PolicyNumber'
                               PASSING xml_data RETURNING CONTENT)
                      AS VARCHAR2(14))),
   effective_date AS (XMLCast(XMLQuery('/PolicyMessage/Policy/PolicyEffectiveDate'
                               PASSING xml_data RETURNING CONTENT)
                      AS DATE )),
   expiration_date AS (XMLCast(XMLQuery('/PolicyMessage/Policy/PolicyExpirationDate'
                               PASSING xml_data RETURNING CONTENT)
                      AS DATE ))
   );

-- The above works fine.

Now if I want to add a new Virtual Column, the only way appears to be to drop and re-create the table which might not be easy down the line.

Answer 1

Looks like you can:

SQL> CREATE TABLE Import_Log (message_guid varchar2(36),
  2                   xml_data XMLType)
  3    XMLTYPE xml_data STORE AS BINARY XML
  4    VIRTUAL COLUMNS
  5    (policynumber AS (XMLCast(XMLQuery('/PolicyMessage/Policy/PolicyNumber'
  6                                 PASSING xml_data RETURNING CONTENT)
  7                        AS VARCHAR2(14))),
  8     effective_date AS (XMLCast(XMLQuery('/PolicyMessage/Policy/PolicyEffectiveDate'
  9                                 PASSING xml_data RETURNING CONTENT)
 10                        AS DATE ))
 11  );

Table created.

SQL> 
SQL> alter table import_log
  2  add (
  3        expiration_date generated always AS (XMLCast(XMLQuery('/PolicyMessage/Policy/PolicyExpirationDate'
  4                                 PASSING xml_data RETURNING CONTENT)
  5                        AS DATE )) virtual
  6  )
  7  ;

Table altered.