Hi,
I am on Oracle 11gR2. I have a text index created on my table column, and when I am passing parameter as comma (,) or semicolon (;), etc. it gives me error Oracle Text error: DRG-50901: text query parser syntax error.
I know a solution is to escape these characters using '\', but this search string is entered by user and I don't know what all characters, I need to escape. Can anyone help me on how to escape all the reserved characters?
Code:
CREATE TABLE MY_LOGS
(
job_id NUMBER,
log_program VARCHAR2(100),
log_parameters VARCHAR2(2000),
search_logs_flag VARCHAR2(1)
);
CREATE OR REPLACE TRIGGER BIU_MY_LOGS
before insert OR UPDATE on MY_LOGS
for each row
begin
:NEW.SEARCH_LOGS_FLAG := 'Y';
end;
/
-- create preferences
ctx_ddl.create_preference('MY_LEXER','BASIC_LEXER');
ctx_ddl.set_attribute('MY_LEXER', 'printjoins', '-_');
ctx_ddl.create_preference('MULTI_SEARCH_LOGS','MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('MULTI_SEARCH_LOGS',
'columns',
'job_id, log_program, log_parameters');
-- create text indexes
CREATE INDEX MY_LOGS_CTX1
ON MY_LOGS (SEARCH_LOGS_FLAG)
INDEXTYPE IS ctxsys.context
PARAMETERS ('DATASTORE MULTI_SEARCH_LOGS sync (on commit) LEXER my_lexer');
-- Query that gives mentioned error is
SELECT *
FROM MY_LOGS
where CONTAINS (SEARCH_LOGS_FLAG, ',', 1) > 0
Thanks.