Hi All,
I've tried the following in DB 11.2:
drop table ot_test;
create table ot_test(
id number primary key,
id_f varchar2(20),
prod_name varchar2(1000),
search_keywords varchar2(2000),
prod_description_short varchar2(2000));
begin
insert into ot_test values(1,101,'Laptops and Desktops','laptops,desktop','A laptop computer or notebook computer, also laptop or notebook for short, is a small, portable personal computer (PC) that is designed to be practically placed on the user''s lap, hence the name');
insert into ot_test values(2,102,'Antivirus Softwares','Antivirus,software','Antivirus software (abbreviated to AV software), also known as anti-malware, is a computer program used to prevent, detect, and remove malware. Antivirus software was originally developed to detect and remove computer viruses, hence the name. However, with the proliferation of other malware, antivirus software started to protect against other computer threats.');
insert into ot_test values(3,103,'sAAS products','saas, products','A SaaS product is an internet software that is accessible to all users. SaaS products are everywhere.');
insert into ot_test values(4,104,'Cloud computing','Cloud,computing','Cloud computing is the on-demand availability of computer system resources, especially data storage and computing power, without direct active management by the user.');
insert into ot_test values(5,105,'Optical mouse','Optical, mouse','An optical mouse is a computer mouse which uses a light source, typically a light-emitting diode, and a light detector, such as an array of photodiodes, to detect movement relative to a surface.');
end;
/
commit;
select * from ot_test;
create index ot_id_f on ot_test(id_f) indextype is ctxsys.context parameters ('sync (on commit)');
create index ot_prod_name on ot_test(prod_name) indextype is ctxsys.context parameters ('sync (on commit)');
create index ot_search_keywords on ot_test(search_keywords) indextype is ctxsys.context parameters ('sync (on commit)');
create index ot_prod_description_short on ot_test(prod_description_short) indextype is ctxsys.context parameters ('sync (on commit)');
select * from user_indexes where table_name = 'OT_TEST';
SELECT id,
id_f,
prod_name,
search_keywords,
prod_description_short,
score(1) sc_1,
score(2) sc_2,
score(3) sc_3,
score(4) sc_4
FROM ot_test
where
(
(
(
:P2_SEARCH_TERM is not null and
contains ( id_f,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT">' ||:P2_SEARCH_TERM || '
<progression>
<seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
<seq><rewrite>transform((TOKENS, "{", "}", " NEAR "))</rewrite></seq>
</progression>
</textquery>
<score datatype="FLOAT" algorithm="COUNT"/>
</query>',
1 ) > 0
)
or :P2_SEARCH_TERM is null
)
or
(
(
:P2_SEARCH_TERM is not null and
contains ( prod_name,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT">' || :P2_SEARCH_TERM || '
<progression>
<seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
<seq><rewrite>transform((TOKENS, "{", "}", " NEAR "))</rewrite></seq>
</progression>
</textquery>
<score datatype="FLOAT" algorithm="COUNT"/>
</query>',
2 ) > 0
)
or :P2_SEARCH_TERM is null
)
or
(
(
:P2_SEARCH_TERM is not null and
contains ( search_keywords,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT">' || :P2_SEARCH_TERM || '
<progression>
<seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
<seq><rewrite>transform((TOKENS, "{", "}", " NEAR "))</rewrite></seq>
</progression>
</textquery>
<score datatype="FLOAT" algorithm="COUNT"/>
</query>',
3 ) > 0
)
or :P2_SEARCH_TERM is null
)
or
(
(
:P2_SEARCH_TERM is not null and
contains ( prod_description_short,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT">' || :P2_SEARCH_TERM || '
<progression>
<seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
<seq><rewrite>transform((TOKENS, "{", "}", " NEAR "))</rewrite></seq>
</progression>
</textquery>
<score datatype="FLOAT" algorithm="COUNT"/>
</query>',
4 ) > 0
)
or :P2_SEARCH_TERM is null
)
)
order by sc_1 desc, sc_2 desc, sc_3 desc, sc_4 desc;
Questions:
The query is returning results as expected but If I provide blank (null) search term or special characters like (@, #, $ etc.); then it returns ORA-20000: Oracle Text error. I need help in solving these scenarios.
Thanks.