DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How can I get the row count?

  1. #1
    Join Date
    Sep 2001
    Posts
    99
    if I use a statement as follows in a procedure.
    update tablename set a = 'aa' where c='dd';
    How can I know how many rows were updated?
    Please help.
    thanks.
    ocean

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    I always have seen after the update:

    n files updated

    Regards

    Angel

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Here is how according to Metalink note 97874.1:


    DECLARE
    updt_count NUMBER;
    BEGIN


    UPDATE updt_rec
    SET col1 = 500,
    col2 = 'Y'
    WHERE col1 <= 25;

    SELECT COUNT(*)
    INTO updt_count
    FROM updt_rec
    WHERE col1 <= 25;

    DBMS_OUTPUT.PUT_LINE('No of records updated = '|| updt_count);

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(' Program failed');
    END;
    /



  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    If you mean a PL/SQL procedure then use the ROWCOUNT implicit cursor attribut:
    Code:
    ...
    update tablename set a = 'aa' where c='dd';
    dbms_ouput.put_line(sql%ROWCOUNT||' rows updated');
    ...

  5. #5
    Join Date
    Sep 2001
    Posts
    99

    thanks ales

    thanks ales.
    the sql%rowcount in oracle is eaqul to @@rowcount in sql server.
    thanks very much.
    ocean

  6. #6
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by aarroyob
    I always have seen after the update:

    n files updated

    Regards

    Angel
    Sorry, I think I was a little bit sleep :-)

    Regards

    Angel

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    Talking Re: thanks ales

    Originally posted by oceanju
    the sql%rowcount in oracle is eaqul to @@rowcount in sql server.
    I'd better say "@@rowcount in sql server is equal to the sql%rowcount in oracle" ;-)

Posting Permissions

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


Click Here to Expand Forum to Full Width