Update column into Virtual Columns oracle

Asked Oct 29 '18 at 16:15

Active Oct 29 '18 at 16:58

Viewed 1,357 times

I would like to modify an existing column of my table in a virtual column. By opening the "SQL" tab in Oracle SQL Developer, the current column is (table DDL):

"ENABLED" NUMBER(1,0) DEFAULT '1'

I would like to convert this column to a virtual column and get the following result:

"ENABLED" NUMBER(1,0) GENERATED ALWAYS AS ("SCHEMA-NAME"."PRODUCT_ENABLED"("PRODUCT_ID")) VIRTUAL VISIBLE

To do this I wrote the following query:

  ALTER TABLE SCHEMA-NAME.SP_PRODUCT MODIFY ENABLED NUMBER(1,0) GENERATED ALWAYS AS ("SCHEMA-NAME"."PRODUCT_ENABLED"("PRODUCT_ID")) VIRTUAL VISIBLE;

I'm not sure this is the right way to go, could you help me find the right solution?

edited Oct 29 '18 at 16:58

asked Oct 29 '18 at 16:15

Maurizio Rizzo

1

Did this approach work? – Gordon Linoff Oct 29 '18 at 16:18
I do not have the ability to immediately run the update query. I would like to know first if it was correct or not. – Maurizio Rizzo Oct 29 '18 at 16:22
. . I haven't don't this particular action. I would expect that you would need to drop the column first and then add it again as a generated column. – Gordon Linoff Oct 29 '18 at 16:48
I do not know how to do this – Maurizio Rizzo Oct 29 '18 at 16:58
1

`"SCHEMA-NAME"."PRODUCT_ENABLED"` should be a deterministic function. – Kaushik Nayak Oct 30 '18 at 05:11

Update column into Virtual Columns oracle

0 Answers0