Limiting amount of rows returned from a query.

Hi. Is there a way that I can limit the number of rows returned by a query to eg. 2000?

I need to do this due to a bug in Oracle - when the resultset is above X rows, the interMedia/Oracle connection core dumps.

The bug is immediate when I use the ORDER BY clause. And I need to do that.. And that seems a very big problem. Otherwise I could use ROWNUM.

A potential solution is to use the FIRST_ROWS hint. But the query has multiple WHERE clauses, so this hint does not work. Is there something similar to the below that can be done? (Example is from the Oracle Text Tuning Guide).

declare
cursor c is
select /*+ FIRST_ROWS */ pk, score(1), col from ctx_tab where contains(txt_col, 'test', 1) > 0 order by score(1) desc; begin
for i in c
loop
insert into t_s values(i.pk, i.col);
exit when c%rowcount > 2000;
end loop;
end;
/

Your help is much appreciated,

Morten Received on Sat May 11 2002 - 19:05:32 CDT