| Error with Contains Clause in InterMedia [message #185451] |
Tue, 01 August 2006 16:32  |
w951unn
Messages: 1
Registered: August 2006
Location: United States
|
Junior Member |
|
|
I am trying to get a query that parses out a CLOB ( XML tags) to work but I get an error when I use an OR with the Contains clause.
Error:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error;
DRG-50901: text query parser syntax error on line 1, column 2
Part of the query that I am using is as follows :
SELECT
a.subj_key_val_txt, func_ctct_hist_xml_parse(XMLTYPE(a.adt_xml_desc).extract('/EVENT/CONTACT_HISTORY'), 'csrFirstName'), func_ctct_hist_xml_parse(XMLTYPE(a.adt_xml_desc).extract('/EVENT/CONTACT_HISTORY'), 'csrLastName'),
a.adt_crt_dtm adt_crt_dtm, func_ctct_hist_audit_event(ae.adt_evt_typ_name,(XMLTYPE(a.adt_xml_desc).extract('/EVENT')) )
FROM
audit_v3 a,
audit_evt_type_v3 ae
WHERE
a.adt_evt_typ_id = ae.adt_evt_typ_id and
(
CONTAINS(a.adt_xml_desc, to_char(cons_seq_nbr_in) || ' WITHIN CONS_SEQ_NBR OR ' || to_char(member_id_in) || ' WITHIN (MEMBER_ID WITHIN CONTACT_HISTORY)') > 0)
and
trunc(a.adt_crt_dtm) between start_date_in and end_date_in
*** I have also tried to use a "label" but that did not work either
CONTAINS(a.adt_xml_desc, to_char(cons_seq_nbr_in) || ' WITHIN CONS_SEQ_NBR', 1) > 0 or
CONTAINS(a.adt_xml_desc, to_char(member_id_in) || ' WITHIN MEMBER_ID', 2) > 0
Any help is truely appreciated.
|
|
|
|
| Re: Error with Contains Clause in InterMedia [message #185455 is a reply to message #185451] |
Tue, 01 August 2006 17:52  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
What version of Oracle are you using? Can you post some code to reproduce the error, like your table creations, insert statemets for sample data, index creation, and section groups, and any datastores or preferences or other things that might be relevant? It helps if you can narrow down the source of the error. If you just select from audit_v3 with the contains clause with hard-coded values and no other filters or join conditions do you still get the error? Does it work with two WITHIN clauses and OR, but without a nested WITHIN clause? Does the same code work in another version? In one of my responses here:
http://forums.oracle.com/forums/thread.jspa?messageID=1383232�
I noted that the multiple contains clauses with OR worked in 9i, although it was inefficient, but produced an error in 10g. It looks like you may have encountered something similar. If you can provide enough to reproduce the error in your situation, perhaps I can come up with a workaround. I found it rather comical that all the documentation says about your error is:
"DRG-50901: text query parser syntax error on line string, column string
Cause: bad query
Action: fix query"
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
