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.
To be precise, the date type stores timestamps without any timezone information rather than "in local time zone".
How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?
You have three options:
index the date and convert your timestamp to date when querying, eg:
select * from foo where foo_date=cast(systimestamp as date) from dual;
create a functional index on the date converted to a timestamp with local time zone, eg:
create unique index i_foo on foo(cast(ts as timestamp with local time zone));
select * from foo where cast(ts as timestamp with local time zone)=
cast(X as timestamp with local time zone);
change the type of your columns of type date to timestamp with time zone or timestamp with local time zone
Of these, it is very likely that the last option is the best one - timestamp with local time zone is the 'correct' type for most data that refers to a fixed point in time. (As opposed to 'noon' which is always 12:00 no matter which time zone you are in.)
Best Answer
VSIZE
select max(vsize(mycolumn)) from mytable;