I’ve a problem using timestamp type in oracle facet query.
I created this table
CREATE TABLE test_user_ft (
id VARCHAR2(10) NOT NULL,
name VARCHAR2(50),
surname VARCHAR2(50),
age number,
date_birth timestamp
);
then I created the following datastore and section group for a Full Text Search and facet query
BEGIN
ctx_ddl.drop_preference (‘test_user_ft_ds’);
ctx_ddl.create_preference(‘test_user_ft_ds’, ‘MULTI_COLUMN_DATASTORE’);
ctx_ddl.set_attribute (‘test_user_ft_ds’, ‘COLUMNS’, ‘id, name, surname, age’);
ctx_ddl.drop_section_group ('test_user_ft_sg');
ctx_ddl.create_section_group ('test_user_ft_sg', 'BASIC_SECTION_GROUP');
ctx_ddl.add_sdata_section ('test_user_ft_sg', 'date_birth ', 'date_birth ', 'TIMESTAMP');
END;
And finally the index
CREATE INDEX test_user_ft_index ON test_user_ft (id) INDEXTYPE IS ctxsys.context PARAMETERS (‘datastore test_user_ft_ds section group test_user_ft_sg sync(on commit)’);
My question is: how can I use “date_birth” field value format in a Oracle Text query for a facet search using SDATA function?
In Oracle APEX App Builder user guide I found the following sentence:
“For DATE or TIMESTAMP values, use the YYYYMMDDHH24MISS canonical format”
but I use some query like these
SELECT * FROM TEST_USER_FT WHERE CONTAINS(ID,’SDATA(DATE_BIRTH > “20100131020000”)’)>0;
I obtain Oracle Error:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-12246: SDATA operand not compatible with the sdata section DATE_BIRTH
29902. 00000 – “error in executing ODCIIndexStart() routine”
Can someone help me? What is the right format value to use?
Thank you
Can someone help me?
Domenico is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.