Indexing multiple fields with Oracle TEXT (Intermedia)

[Philippe LAVIGERIE]

What's wrong with my sample ?

SQLselect * 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 ;
SQLselect count(*) from tmp_objects;

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

create or replace procedure idx_fields_as_o ne_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.writea ppend( p_lob, length(c1.OWNER )
+length(c1.OBJE CT_NAME)
+length(c1.SUBO BJECT_NAME)
+length(c1.OBJE CT_ID)
+length(c1.DATA _OBJECT_ID)
+length(c1.OBJE CT_TYPE)
+length(TO_CHAR (c1.CREATED,'YY YYMMDD'))
+length(TO_CHAR (c1.LAST_DDL_TI ME,'YYYYMMDD'))
+length(c1.TIME STAMP)
+length(c1.STAT US)
+length(c1.TEMP ORARY)
+length(c1.GENE RATED)
+length(c1.SECO NDARY)
+ nb_fields,
c1.OWNER || ' ' ||
c1.OBJECT_NAME || ' ' ||
c1.SUBOBJECT_NA ME || ' ' ||
c1.OBJECT_ID || ' ' ||
c1.DATA_OBJECT_ ID || ' ' ||
c1.OBJECT_TYPE || ' ' ||
TO_CHAR(c1.CREA TED,'YYYYMMDD') || ' ' ||
TO_CHAR(c1.LAST _DDL_TIME,'YYYY MMDD') || ' ' ||
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_pr eference('tmp_u ser_datastore') ;
ctx_ddl.create_ preference( 'tmp_user_datas tore', 'user_datastore '
);
ctx_ddl.set_att ribute( 'tmp_user_datas tore', 'procedure',
'idx_fields_as_ one_clob' );
end;
/

begin
-- ctx_ddl.drop_pr eference('my_le xer');
ctx_ddl.create_ preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_att ribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_att ribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/

drop index foo2_idx;
create index foo2_idx on tmp_objects(dum my_col) indextype is
ctxsys.context parameters( 'datastore tmp_user_datast ore lexer
my_lexer');

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

Thank's for your help !

[Xavier]

Re: Indexing multiple fields with Oracle TEXT (Intermedia)

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.