Indexing multiple fields with Oracle TEXT (Intermedia)

From: Philippe LAVIGERIE <plavigerie_at_transiciel.com>
Date: 22 Aug 2003 09:32:27 -0700
Message-ID: <6ab5ef89.0308220832.976786d_at_posting.google.com>

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 ! Received on Fri Aug 22 2003 - 18:32:27 CEST