Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    69

    Unanswered: ROWCOUNT facility

    There is this SET ROWCOUNT statement in SQL Server to limit the number of rows to be processed. What is its corresponding equivalent in Oracle ? Do we have to play with ROWNUM ? But then in this case I am not sure how to simulate the behaviours for UPDATE and INSERT statements.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understood well, in SQL Server you can do this:
    Code:
    set rowcount 10
    update mytable set mycolumn = some_value
      where another_column > 20;
    and if, let's say, 1000 rows satisfy this condition, only 10 rows will be updated?

    If so, well, as far as I know, there's nothing like this in Oracle. You could, however, write a procedure using cursor and exit loop if cursor%rowcount exceeds limit. For example,
    Code:
    DECLARE
      CURSOR c1 IS SELECT empno FROM EMP;
    BEGIN
      FOR c1r IN c1 LOOP
        EXIT WHEN c1%rowcount = 10;
        dbms_output.put_line(c1r.empno);
      END LOOP;
    END;
    /

  3. #3
    Join Date
    Apr 2004
    Posts
    69
    Thanks for your response and it was useful !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •