0

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

Zach Ross-Clyne
  • 779
  • 3
  • 10
  • 35

0 Answers0