Database Software

I'm having trouble tracking down an error when using oracle text on a non-materialized view (indexes are on the referenced columns). My database has a users table and a user history table which saves the old values when a user profile changes. My view performs a "union all" so I can select from both at once.

I would like to use oracle text to perform a "contains" on the view whenever someone signs up to see if any current users or historical entries contain the desired username.

The following works fine:

contains(user_history_view, 'bill')

but when I reference anything in the contains clause, i get a "column is not indexed" error:

contains(user_history_view, signup.user_name) --signup.username is 'bill'

Here is a stripped-down demonstration (I am using version 10.2.0.4.0)

create table signup (
  signup_id   number(19,0) not null,
  signup_name varchar2(255),
  primary key (signup_id)
);

create table users (
  user_id   number(19,0) not null,
  user_name varchar2(255),
  primary key (user_id)
);

create table user_history (
  history_id number(19,0) not null,
  user_id    number(19,0) not null,
  user_name  varchar2(255),
  primary key (history_id),
  foreign key (user_id) references users on delete set null
);

create index user_name_index on users(user_name) 
indextype is ctxsys.context parameters ('sync (on commit)');

create index user_hist_name_index on user_history(user_name) 
indextype is ctxsys.context parameters ('sync (on commit)');

create index signup_name_index on signup(signup_name) 
indextype is ctxsys.context parameters ('sync (on commit)');

create or replace force view user_history_view 
(user_id, user_name, flag_history) as
  
select user_id, user_name, 'N' from users
union all
select user_id, user_name, 'Y' from user_history;

--user bill changed his name to bob, and there is a pending signup for another bill
insert into users(user_id, user_name) values (1, 'bob');
insert into user_history(history_id, user_id, user_name) values (1, 1, 'bill');
insert into signup(signup_id, signup_name) values(1, 'bill');

commit;

--works
select * from user_history_view users, signup new_user
where new_user.signup_id = 1 
and contains(users.user_name, 'bill')>0;

--fails
select * from user_history_view users, signup new_user
where new_user.signup_id = 1 
and contains(users.user_name, new_user.signup_name)>0;

I could move everything into a materialized view, but querying against real-time data like this would be ideal. Any help would be greatly appreciated.