Cursor Query

In pre-8.1, you use a cursor query, formerly known as an in-memory query, over a Text query when you want only a small portion of a potentially large hitlist.

In 8.1, the PL/SQL interface for in-memory queries is obsolete. This means that the following procedures are obsolete in 8.1:

• CTX_QUERY.OPEN_CON

• CTX_QUERY.FETCH_HIT

• CTX_QUERY.CLOSE_CON

• CTX_QUERY.COUNT_LAST

To migrate pre-8.1 in-memory queries, use a cursor. Use the FIRST_ROWS hint in the SELECT statement to obtain the first n hits of a potentially large hitlist.

Pre-8.1 Method

The following in-memory query finds all documents that contain the word oracle and returns them in score sorted order. The mechanism of the query returns the hits row by row in order, thus allowing you to extract the first n hits without spending the overhead of obtaining the entire hitlist first.

declare 
  pk   varchar2(80); 
  scr  number; 
  cur  number; 
begin 
  cur := ctx_query.open_con('mypolicy','oracle',TRUE); 
  while (ctx_query.fetch_hit(cur, pk, scr) > 0) 
  loop 
    -- deal with hit 
  end loop; 
  ctx_query.close_con(cur); 
end; 

8.1 Method

The pre-8.1 cursor query procedures OPEN_CON, FETCH_HIT, CLOSE_CON, COUNT_LAST are obsolete in 8.1.

To obtain the first n hits of a potentially large hitlist, execute the CONTAINS query using a cursor. Use the FIRST_ROWS hint to optimize for response time in the SELECT statement as follows:

begin 
  for c1 in (select /*+ FIRST_ROWS */ pk, score(1) scr 
               from basetable 
              where contains(textcol, 'oracle', 1) > 0 
              order by scr desc) 
  loop 
    -- deal with hit 
    dbms_output.put_line('KEY is '||c1.pk); 
    dbms_output.put_line('SCORE is '||c1.scr); 
  end loop; 
end;

See Also: To learn more about using the FIRST_ROWS hint with CONTAINS queries, see the Oracle8i interMedia Text Reference.