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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 ' );
|
|
|
|
Goto Forum:
Current Time: Wed May 08 14:15:41 CDT 2024
|