Indexing multiple fields with Oracle TEXT (Intermedia)

[Philippe LAVIGERIE]

What's wrong with my sample ?

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

connect ctxsys/ctxsys1

create table tmp_objects as select * from sys.dba_objects;
SQL> select count(*) from tmp_objects;

COUNT(*)
----------
28705

create or replace procedure idx_fields_as_one_clob(
p_id in rowid,
p_lob IN OUT clob
)
is
nb_fields number := 12;
begin
for c1 in (select * from tmp_objects where rowid = p_id)
loop
-- assemble into p_lob ANY data you want
-- indexed.
dbms_lob.writeappend( p_lob, length(c1.OWNER)
+length(c1.OBJECT_NAME)
+length(c1.SUBOBJECT_NAME)
+length(c1.OBJECT_ID)
+length(c1.DATA_OBJECT_ID)
+length(c1.OBJECT_TYPE)
+length(TO_CHAR(c1.CREATED,'YYYYMMDD'))
+length(TO_CHAR(c1.LAST_DDL_TIME,'YYYYMMDD'))
+length(c1.TIMESTAMP)
+length(c1.STATUS)
+length(c1.TEMPORARY)
+length(c1.GENERATED)
+length(c1.SECONDARY)
+ nb_fields,
c1.OWNER || ' ' ||
c1.OBJECT_NAME || ' ' ||
c1.SUBOBJECT_NAME || ' ' ||
c1.OBJECT_ID || ' ' ||
c1.DATA_OBJECT_ID || ' ' ||
c1.OBJECT_TYPE || ' ' ||
TO_CHAR(c1.CREATED,'YYYYMMDD') || ' ' ||
TO_CHAR(c1.LAST_DDL_TIME,'YYYYMMDD') || ' ' ||
c1.TIMESTAMP || ' ' ||
c1.STATUS || ' ' ||
c1.TEMPORARY || ' ' ||
c1.GENERATED || ' ' ||
c1.SECONDARY
);
end loop;
end;
/

alter table tmp_objects add dummy_col varchar2(1);

begin
-- ctx_ddl.drop_preference('tmp_user_datastore');
ctx_ddl.create_preference( 'tmp_user_datastore', 'user_datastore'
);
ctx_ddl.set_attribute( 'tmp_user_datastore', 'procedure',
'idx_fields_as_one_clob' );
end;
/

begin
-- ctx_ddl.drop_preference('my_lexer');
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/

drop index foo2_idx;
create index foo2_idx on tmp_objects(dummy_col) indextype is
ctxsys.context parameters( 'datastore tmp_user_datastore lexer
my_lexer');

select * from tmp_objects where contains( dummy_col, 'JAVA', 1 ) > 0 ;
SQL> No rows selected

Thank's for your help !

[Xavier]

I don't see anything wrong with your procedure, but remember that when
you create the index, it does not instantaneously contain entries for
all existing data. The CTXSYS system indexes content on a separate
process, and sometimes it can take a while. This assumes that the row
with the 'JAVA' word was pre-existing. If you add it later, you need
to synch the index.