Using bind variables with Execute Immediate and context index

Oracle V9.2 on HP-UX I think

I am trying to write a stored procedure that does a lookup on a list of words supplied as a parameter in a pipe delimited string.

The lookup has to include a call to a Context style text index.

If I do the following, it works
v_search_string is a VARCHAR2 containing a single word extracted from the incoming pipe delimited parameter
v_temp_string := 'INSERT INTO BULK_SEARCH_RESULTS SELECT lil_record_id,''' ||v_search_string|| ''' term FROM TERM_LEVEL WHERE'; v_temp_string := v_temp_string || ' language_code = :x and contains
(dummy_im, ''({v_search_string}) WITHIN TERM '') > 0';
EXECUTE IMMEDIATE v_temp_string USING p_lang_code;

The above code executes OK but is not ideal as this will be executed 10s of times a day and may contain 100s of words on each occasion so the use of bind variables for the context index would be a good idea. However, if I try this:
v_temp_string := 'INSERT INTO BULK_SEARCH_RESULTS SELECT lil_record_id,''' ||v_search_string|| ''' term FROM TERM_LEVEL WHERE'; v_temp_string := v_temp_string || ' language_code = :x and contains
(dummy_im, ''({:y}) WITHIN TERM '') > 0';
EXECUTE IMMEDIATE v_temp_string USING p_lang_code, v_search_string;

It fails with an error that indicates that it can't find a bind variable. Am I missing something here or is it impossible to use bind variables in this way with a text index?

On the subject of text indexes, does anyone know if it is possible to make a search on a context text index that only returns an exact match. This is what we really want exact match only but with the the facility provided within text indexing to find alternative spellings in German and some other languages.

Also on the subject of text indexes, does anyone know if the facilities to find alternative spellings can be turned off. It seems to return some fairly silly alternative spelling matches on occasions and some of our user base don't like it for that reason.

TIA
Paulos Received on Mon Nov 07 2005 - 14:11:00 CST