How to drop system generated virtual column in a table in12c

You might also consider using the listagg function on the column_names and drop them all at the same time.

Thanks for the info. I ran it.
It complains that "PLS-00302: component 'VIRTUAL_COLUMN' must be declared", which is "v_sql := 'alter table '||c.table_name||' drop '||c.virtual_column;"  Why?

slightwv, thanks for catching my typo.

Thanks a lot for the info.
I guess those columns are from collecting stat. I got the problem because when I inserted to the table, I got errors and had to consider some values for the virtual columns, which I had no idea.

>>I got the problem because when I inserted to the table

This is why you need to always include column names on the inserts.

Instead of:
insert into some_table values('a');

You need to write all your code to do:
insert into some_table(some_column) values('a');

Then as new columns are added to the table, the code doesn't break.

>>I guess those columns are from collecting stat

I've never seen virtual columns added to a table based on system generated stats.  Are you sure they are Oracle system generated or some application running against the database is the 'system' that generated them?

Now it complains about the line: "execute immediate v_sql;"
---
ORA-00942: table or view does not exist
ORA-06512: at line 7
---

can they be dropped?
I manually ran the query: "select table_name, column_name from user_tab_cols where virtual_column = 'YES' and table_name = <yourtable>" and got 2 columns back. They are there.

For two columns in one table, just drop them?  Why worry about the PL/SQL loop?

I'm still going to suggest that if something added those columns, then something might need them.  Dropping them might break things.

Anyway, it looks like another simple typo.  These can be fixed by looking at the syntax of the alter table drop column syntax and correcting the code.

I'm not sure why it is generating a ORA-00942 but I think there is another syntax issue:
v_sql := 'alter table '||c.table_name||' drop column '||c.column_name;


You can check the syntax by adding this above the execute immeidate:
dbms_output.put_line(v_sql);

You can then verify the SQL has the correct syntax.

The error messages were a bit misled. The error messages are actually
--
ORA-12996: cannot drop system-generated virtual column
ORA-06512: at line 7
--

Well, there you go.  You cannot drop them so you'll need to fix all the code that does the inserts.

I've seen some references that the virtual column might be due to function based indexes.  If that is what you have, then dropping them could be a really bad idea.

My suspicions are that someone has create extended statistics, which is used for optimizer enhancements. The following link provides  good description and explanation of its values.

https://blogs.oracle.com/optimizer/entry/extended_statistics

Thanks for all the tips and info. Since the "extra" columns prevented "inserting", the table was rebuilt to remove the columns. At least now, the insert is OK, but do not know how long it lasts.

Thanks for the effort.