You might want to look into the MARKUP
or HIGHLIGHT
functions of the CTX_DOC
package. An example of how MARKUP
can be used for your query (this assumes your index is called idx_news
and you have an ID
column):
declare
MARKLOB clob;
MARK1 number;
MARK2 number;
cursor NEWS_SEARCH is
select * from NEWS;
begin
for REC in NEWS_SEARCH loop
CTX_DOC.MARKUP('idx_news', to_char(REC.ID), 'people OR country', MARKLOB);
MARK1 := instr(MARKLOB, '<<<');
MARK2 := instr(MARKLOB, '>>>');
if MARK1 + MARK2 <> 0 then
dbms_output.
PUT_LINE(
REC.ID || ',' || substr(MARKLOB, MARK1 + 3, MARK2 - MARK1 - 3));
end if;
end loop;
end;
/
What it does is mark up your text (surround the found items with <<<>>>
) and outputs that new text to a clob
which is then searched for the markup. All text that is found in your query will be marked up but my PL/SQL will only display the first result; it can be adapted to find them all and display only the unique ones. This is a simple example and perhaps can become too complicated for your original query but it's here to point the way.
Alternatively, a simple SELECT
may suffice if all you have is an OR
in your text query:
select decode(sign(SCORE(1))
,1, decode(sign(SCORE(2)), 1, 'both', 'people')
,'country')
as WHICH
,TITLE
from NEWS A
where CONTAINS(TITLE, 'people', 1) > 0 or CONTAINS(TITLE, 'country', 2) > 0;
This checks the score for individual searches on 'people' and 'country' and works out which ones were positive, returning 'both' if both search terms were found.
In #1 and #2, something uses the output of test_function, but in #3 nothing uses it. The optimizer knows this and does not call test_function. Since test_function isn't called, the side effect of updating the table does not happen.
This is definitely intended behavior.
I rewrote your #3 to the following, and test_function got called (the UPDATE happened). The reason is that the result of test_function was needed for output.
SELECT 1, tf FROM dual
INNER JOIN (SELECT test_function as tf FROM dual)a ON (1=1)
;
However, generally speaking relying on the side effect of a function to do real work from inside a SQL query is not a good idea. The optimizer is always looking for a reason to do as little work as possible, and you might be surprised sometimes to learn that the optimizer sees the necessity of your function differently than you do.
Best Answer
Probably not
The view that may display native stored compiled indexes is CTX_INDEX_VALUES Or CTX_USER_INDEX_VALUES (depending on permissions and querying user). However, given that the index value length is only 500 characters, it is unlikely that it is stored there. (Just start selecting randomly from those indexes, if it exists, it will likely be there.)
Looking at the view design, however, it is unlikely that those virtual XML elements are stored, as it's much easier to compile them on the fly and then load them into memory.
Alternatively, CTX_DOC has a number of markup functions, but none indicate the ability to access native XML.
You may also want to explore the discussion around metalink: 122255.1 (Unfortunately, I don't currently have metalink access).
It may be easier to write your own parser-to-XML (as oracle has quite a few excellent native XML functions) than try to hack around the full text index.