Re: How to 'stop counting' past 100 lines?

As soon as you include an order by
or group by in your query you are in
trouble.

However (v 8.1), if you call a PL/SQL routine to bulk collect into a varray type with a limit of 100, and sort the varray after it has been populated, then Oracle will raise
an error if you attempt load more than 100 rows into the array.

In this way you can get sorted data if there are less than 100 rows, and an error message if there are going to be more than 100 rows.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Pete Reeves > wrote in message
<975580401.15710.0.nnrp-13.d4e467b6_at_news.demon.co.uk>...


>I'm trying to write an address searching routine, but if the search will

>return > 100 lines I want the user to refine the search.

>It works fine by doing a count before returning the results, but

 considering


>we have 3 million records, the count takes too long - the 100 lines max is

>to prevent this time wasting.

>

>Question is, how can I get oracle to 'bomb out' if the count passes 100,

 ie.


>dont keep counting to 3 million becuase I want to return nothing to the

 user


>and get them to refine the search.

>

>Thanks in advance.

>Pete Reeves.

>

>