Re: How to limit rows in PL/SQL?

From: Leonard F Clark <lfc_at_zoom.co.uk>
Date: Sat, 21 Apr 2001 16:01:41 GMT
Message-ID: <3ae1a8f0.11628190@125.0.0.1>

If you really must, then you need to use a cursor. Something like:

create or replace XYZ AS

   nCount INTEGER;

   cursor curLimit IS

     SELECT f1
        FROM table_name;

BEGIN
    nCount := 0;
    FOR vLimit IN curLimit LOOP

       IF nCount = <some_set_value> THEN
          EXIT
       ELSE
          DBMS_OUTPUT.PUT_LINE( vLimit.Col1||','||vLimit.Col2|| ....);
          nCount := nCount + 1;
       END IF;

    END LOOP;
END;
/

Of course, you can elaborate on this.

Len

>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
>
>
>
Received on Sat Apr 21 2001 - 11:01:41 CDT