Home » Server Options » Text & interMedia » Show Markup of Searced Multi-Column Text (Oracle9i 9.2.0.6.0 )
Show Markup of Searced Multi-Column Text [message #438503] Sun, 10 January 2010 00:14 Go to next message
ora22
Messages: 8
Registered: December 2009
Location: BOSTON
Junior Member
We have a table as

create table test_tab1
(
  eng_id                    number(10),    
  eng_name                  varchar2(100 byte),
  cli_short_name            varchar2(200 byte),
  entity_duns_name          varchar2(100 byte),
  ult_par_duns_name         varchar2(100 byte),
  concat                    char(1) /* placeholder for user datastore */    
);

Our User Interface requirement is

Search (Enter one or more terms) ["pepsi","exxon","tata motors"]
Search in : Eng [x] client [x] entity [ ] ultimate [ ]

user can enter multiple search terms and specify where to search

i have a multi-column datastore and sections setup so that i can issue the above search as

select eng_name, cli_short_name, entity_duns_name, ult_par_duns_name 
from test_tab1 
where contains ( concat,
'((exxon or pepsi or tata motors) within the_eng_name) or 
((exxon or pepsi or tata motors) within the_cli_short_name)',1 ) > 0



and it's all working fine ...

One more requirement from user is when we show the results, the
eng_name
cli_short_name
entity_duns_name
ult_par_duns_name

columns should show where the match occured (by some form of highlighting.

note there are multiple search terms and multiple fields being serached here

Can someone please help !!! Please note that our Oracle version is 9.2

here is the setup code for the example


---------------------------------
-- CREATE MULTI-COLUMN-DATASTORE
---------------------------------
connect ctxsys/<password>
grant execute on ctxsys.ctx_ddl to myschema;
-- only ctxsys can create MULTI_COLUMN preferences in Oracle 10g
begin
  ctx_ddl.drop_preference ( 'gcp_multi_column_datastore' );
end;
/
begin
  ctx_ddl.create_preference ( 'gcp_multi_column_datastore', 'multi_column_datastore' );
  ctx_ddl.set_attribute ( 'gcp_multi_column_datastore', 'columns', 'eng_name, cli_short_name, entity_duns_name, ult_par_duns_name' );
end;

connect myschema/<password>
-------------------
-- CREATE TABLE
------------------
drop table test_tab1;
create table test_tab1
(
  eng_id                    number(10),    
  eng_name                  varchar2(100 byte),
  cli_short_name            varchar2(200 byte),
  entity_duns_name          varchar2(100 byte),
  ult_par_duns_name         varchar2(100 byte),
  concat                    char(1) /* placeholder for user datastore */    
);


TRUNCATE TABLE test_tab1 ;
insert into test_tab1 values (1,'some text with pepsi in it, bytheway it also has exxon in it','test1','test1','test1','');
insert into test_tab1 values (2,'test2','some text with pepsi in it','test2','test2','');
insert into test_tab1 values (3,'test3','test3','some text with exxon in it','test3','');
insert into test_tab1 values (4,'test4','test4','some text with exxon in it','exxon','');

commit;

-----------------------
-- CREATE SECTION GROUP
-----------------------
begin
  ctx_ddl.drop_section_group
    (
       group_name => 'test1_section_group'
    );
end;

begin
  ctx_ddl.create_section_group
    (
       group_name => 'test1_section_group',
       group_type => 'basic_section_group'
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_eng_name',
      tag          => 'eng_name',
      visible      => true
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_cli_short_name',
      tag          => 'cli_short_name',
      visible      => true
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_entity_duns_name',
      tag          => 'entity_duns_name',
      visible      => true
    );
  ctx_ddl.add_field_section
    (
      group_name   => 'test1_section_group',
      section_name => 'the_ult_par_duns_name',
      tag          => 'ult_par_duns_name',
      visible      => true
    );
end;

/


---------------
-- CREATE INDEX
---------------

create index tt_idx1 on test_tab1 ( concat )
  indextype is ctxsys.context
  parameters ( '
    datastore      ctxsys.gcp_multi_column_datastore
    section group  test1_section_group           ' );



Re: Show Markup of Searced Multi-Column Text [message #438506 is a reply to message #438503] Sun, 10 January 2010 00:45 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you were using a newer version, you could use CTX_DOC.SNIPPET, but since you are using 9i, you can jut use CTX_DOC.MARKUP:

http://download.oracle.com/docs/cd/B10501_01/text.920/a96518/cdocpkg.htm#12588


Previous Topic: Best approach for Multi Column text search
Next Topic: Highlighting search results with user_datastore context index
Goto Forum:
  


Current Time: Thu Mar 28 12:51:59 CDT 2024