create table lists (  
    list_id    number generated by default on null as identity  
    constraint lists_list_id_pk primary key,  
    list_name  varchar2(100) not null  
) 

create table list_values (  
    list_id                        number not null  
                                   constraint list_values_list_id_fk  
                                   references lists,  
    code_id                        number not null,  
    code_value                     varchar2(100) not null,  
    in_use                         varchar2(1) default 'Y',  
    constraint list_values_pk primary key (list_id, code_id),  
    constraint in_use_chk check (in_use in ('Y', 'N'))  
)  
organization index 

create table customers (  
    customer_id                    number generated by default on null as identity  
                                   constraint customers_pk primary key,  
    status_id                      number not null,  
    status_code_list_id            invisible generated always as (1),  
    constraint customer_status_lov_fk foreign key (status_code_list_id, status_id)   
    references list_values (list_id, code_id)  
)

begin 
    -- customer status 
    insert into lists (list_id, list_name)  
    values (1, 'Customer status'); 
    -- values for customer status 
    insert into list_values (list_id, code_id, code_value)  
    values (1, 1, 'Active');  
    insert into list_values (list_id, code_id, code_value)  
    values (1, 2, 'Terminated'); 
 
    -- some other list 
    insert into lists (list_id, list_name)  
    values (2, 'Some other list');  
    -- values for some other list 
    insert into list_values (list_id, code_id, code_value)  
    values (2, 2, 'Value 1');  
    insert into list_values (list_id, code_id, code_value)  
    values (2, 3, 'Value 3');  
 
    commit;  
end; 

create index customer_status_lov_fk  
on customers (status_code_list_id, status_id) compress

insert into customers (status_id)  
values (1)

insert into customers (status_id)  
values (2)

insert into customers (status_id)  
values (3)

select c.*, v.* 
from customers c join list_values v 
on (c.status_code_list_id = v.list_id and c.status_id = v.code_id)

drop table customers cascade constraints

drop table list_values cascade constraints

drop table lists cascade constraints