Re: Limiting rows returned in SQL statement

Depends on your tool...

From SQL*Worksheet:

set fetchrows ####

Reset with

set fetchrows infinite.

From SQL*PLUS

set pause on

This brings back one page upon each <enter> key press.

You can also select specific rows by limiting the ROWNUM, which is a pseudo column. However, this is very limited.

In PL/SQL, declare a cursor and only selectively fetch the number of rows you want. You can do this in an anonymous block, using the DBMS_OUTPUT.PUT_LINE command to pipe the output to the screen.

Declare
cursor c_foo is
  select * from foo;

foo_rec foo%ROWTYPE;

begin

• fetch, etc.

end;

Christopher Weiss
Professional Services Division
Compuware Corporation

<Salva.Brad_at_DIMAC.COM> wrote in message news:7iek8a$3is$1_at_nnrp1.deja.com...
> What is the syntax (assuming there is a way) to limit the number of
> rows returned by a select statement in Oracle 7 or 8?
>
> I know in Oracle (DEC)RDB there is a limit function -
>
> Select * from TABLENAME limit to XX rows;
>
> What is the syntax for Oracle SQL?
>
> Thanks!
>
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
Received on Tue May 25 1999 - 13:04:37 CDT