Home » SQL & PL/SQL » SQL & PL/SQL » how to change a column to a virtual (Linux RDBMS 12c)
how to change a column to a virtual [message #684256] Wed, 28 April 2021 14:24 Go to next message
evoradba
Messages: 135
Registered: April 2005
Location: Canada
Senior Member
Hello
i need to change a column to a virtual, can someone help

from
FLAG_DATE DATE

to
"FLAG_DATE" DATE GENERATED ALWAYS AS (TRUNC("ORDER_DATETIME")) VIRTUAL ,
Re: how to change a column to a virtual [message #684257 is a reply to message #684256] Wed, 28 April 2021 15:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67883
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't change it, you have to create a new column:
SQL> create table t (id int, ORDER_DATETIME date, FLAG_DATE DATE);

Table created.

SQL> col column_name format a26
SQL> col data_type format a15
SQL> select column_name, data_type, virtual_column, hidden_column
  2  from dba_tab_cols
  3  where owner='MICHEL' and table_name='T'
  4  order by column_id
  5  /
COLUMN_NAME                DATA_TYPE       VIR HID
-------------------------- --------------- --- ---
ID                         NUMBER          NO  NO
ORDER_DATETIME             DATE            NO  NO
FLAG_DATE                  DATE            NO  NO

3 rows selected.

SQL> alter table t rename column FLAG_DATE to FLAG_DATE_OLD;

Table altered.

SQL> select column_name, data_type, virtual_column, hidden_column
  2  from dba_tab_cols
  3  where owner='MICHEL' and table_name='T'
  4  order by column_id
  5  /
COLUMN_NAME                DATA_TYPE       VIR HID
-------------------------- --------------- --- ---
ID                         NUMBER          NO  NO
ORDER_DATETIME             DATE            NO  NO
FLAG_DATE_OLD              DATE            NO  NO

3 rows selected.

SQL> alter table t add (FLAG_DATE DATE GENERATED ALWAYS AS (TRUNC("ORDER_DATETIME")) VIRTUAL);

Table altered.

SQL> select column_name, data_type, virtual_column, hidden_column
  2  from dba_tab_cols
  3  where owner='MICHEL' and table_name='T'
  4  order by column_id
  5  /
COLUMN_NAME                DATA_TYPE       VIR HID
-------------------------- --------------- --- ---
ID                         NUMBER          NO  NO
ORDER_DATETIME             DATE            NO  NO
FLAG_DATE_OLD              DATE            NO  NO
FLAG_DATE                  DATE            YES NO

4 rows selected.

SQL> alter table t set unused column FLAG_DATE_OLD;

Table altered.

SQL> select column_name, data_type, virtual_column, hidden_column
  2  from dba_tab_cols
  3  where owner='MICHEL' and table_name='T'
  4  order by column_id
  5  /
COLUMN_NAME                DATA_TYPE       VIR HID
-------------------------- --------------- --- ---
ID                         NUMBER          NO  NO
ORDER_DATETIME             DATE            NO  NO
FLAG_DATE                  DATE            YES NO
SYS_C00003_21042822:00:07$ DATE            NO  YES

4 rows selected.
And later:
SQL> alter table t drop unused columns;

Table altered.

SQL> select column_name, data_type, virtual_column, hidden_column
  2  from dba_tab_cols
  3  where owner='MICHEL' and table_name='T'
  4  order by column_id
  5  /
COLUMN_NAME                DATA_TYPE       VIR HID
-------------------------- --------------- --- ---
ID                         NUMBER          NO  NO
ORDER_DATETIME             DATE            NO  NO
FLAG_DATE                  DATE            YES NO

3 rows selected.
Re: how to change a column to a virtual [message #684258 is a reply to message #684257] Wed, 28 April 2021 15:03 Go to previous message
evoradba
Messages: 135
Registered: April 2005
Location: Canada
Senior Member
thank you!!!!!
Previous Topic: ORA-00979
Next Topic: substring compare and update
Goto Forum:
  


Current Time: Wed Jun 16 23:34:38 CDT 2021