Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Hidden virtual column created by FBI: Is the column name stable?

User_1871May 17 2022

In a related question, @user-h3j7u mentioned:
If you create a FBI, virtual column is added implicitly.

create table t1(n number);
create index i1 on t1(abs(n));
insert into t1 values (-123);
commit;
col column_name format a15
select column_name, hidden_column, virtual_column from user_tab_cols where table_name='T1';

COLUMN_NAME HID VIR

N NO NO
SYS_NC00002$ YES YES

select n, SYS_NC00002$ from t1;

          N SYS\_NC00002$

       -123          123

Question:
Is the name of that hidden virtual column stable? In other words, when I upgrade from Oracle 18c to the latest version, will the SYS_NC00002$ name change?
Use case:
Possibly explicitly referring to the virtual column name in a query's SELECT clause.

Comments

Alex Nuijten

Why not simply create a virtual column yourself, instead of relying on an implicit functionality?
Then you're in control of the name of the virtual column.

create table t1(n number);
alter table t1
add (abs_n generated always as (abs (n)))
/
create index i1 on t1(abs_n);
2 people found this helpful
User_H3J7U

Is the name of that hidden virtual column stable? In other words, when I upgrade from Oracle 18c to the latest version, will the SYS_NC00002$ name change?
The name is derived from internal_column_id. It changes when the preceding columns are dropped or index recreated.

1 person found this helpful
padders

A common recommendation for function-based indexes which solves the problem of column naming and repeating the potentially complex expression being indexed is to create a view containing the expression as a column and then query the view instead. Whether this is suitable in your case will depend a little on how your environment is maintained and used.

1 person found this helpful
Mike Kutz

3rd party software is preventing the OP from using a proper Virtual Column.
I'm wondering - if that column was HIDDEN, would the SW work?

User_1871

@mike-kutz Yes, I think a hidden virtual column would work. It's a good idea, thanks.
What does SW mean?

1 - 5

Post Details

Added on May 17 2022
5 comments
213 views
2 mentions