Re: How to limit rows in PL/SQL?

On 20 Apr 2001 13:23:11 -0500, "Richard Piasecki" <ogo_at_mailexcite.com> wrote:

>Greetings.
>
>This problem has bothered me for some time, and I've finally decided to see
>if anyone else has a solution to it.
>
>Is there a way to limit the number of rows Oracle returns in a query? For
>example, if I execute a query like:
>SELECT f1 INTO :x
>
>and it returns more than one row, I'll get an exception. I've been told that
>other databases (namely Sybase) provide a variable that can be set to limit
>the number of returned rows to a certain number. The solution I've always
>used in Oracle is to declare a cursor and fetch once from it. Is this the
>only way in Oracle or does anyone know of another method to use?
>
>
>--- Rich
>
>

There are other solutions (where rownum < n) but the solution you are using is the most efficient one.
This is because for a select into Oracle always execute 2 fetches. - the first time to check whether a result exists and fetch it - the second time to check there is *only 1* row (and not more) Your solution is preventing the second fetch. (Also: In a correct situation the second fetch is going to result in ora-1403, no data found. This is what you want, but the error is being generated at the server side, sent in text form to the client, who traps and supresses it. Not necessarily a nice solution)

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Apr 20 2001 - 13:39:57 CDT