I have several tiers of table functions which are used to perform a search. I have a CTXCAT
index on the relevant tables.
When calling one particular function directly I recieve no errors and get the results I am expecting to see
However when I call a function which calls the function that works (mentioned above) I receive a DRG-10849
error
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
ORA-06512: at "SCHEMA.SEARCH", line 857
ORA-06512: at line 1
ORA-06512: at "SCHEMA.SEARCH", line 629
ORA-06512: at line 1
ORA-06512: at "SCHEMA.SEARCH", line 723
ORA-06512: at line 1
ORA-06512: at "SCHEMA.SEARCH", line 1181
ORA-06512: at line 1
SCHEMA.SEARCH
line 857 is:
for r_count in c_count loop
The code for c_count
is
cursor c_count is
select
sum(cnt) cnt
from
(
select
count(*) cnt
from
synonyms
where
P_SEARCH IS NOT NULL AND
catsearch(synonym_value, nvl(p_search, 'a'), '') > 0
union all
select
count(*) cnt
from synonyms
where p_search is null
);
The function this is in is SCHEMA.SEARCH.SYNONYM_SEARCH
. When I call this directly with :
select * from table(search.synonym_search(null))
I get the results I desire
However when I call the following:
select * from table(search.company_dictionary_search(null))
I get the error listed above
The code surrounding the synonym_search
function is as follows:
select ss.score, ct.concept_id
from table(search.synonym_search(p_search)) ss,
term_synonym ts,
concept_term ct,
concept c,
concept_types cts
where ss.table_id = ts.SYNONYMS_ID
and ts.TERM_ID = ct.TERM_ID
and c.concept_id = ct.concept_id
and c.concept_type_id = cts.concept_type_id
I don't know why this is happening but its causing me some major issues, as this is on a live system.
Thanks for any help.
EDIT
The error has only arisen since I added the UNION ALL
to the query