Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2007
    Posts
    21

    Unanswered: number of rows in a cursor?

    Currently I have a pretty simple stored procedure that builds some dynamic SQL

    then returns those results to some code.

    I would like also to be able to return the number of rows in that cursor back to the Java code, how can I do this? Some sample SQL is shown below:

    l_query := 'Select * from customer';
    OPEN searchResults FOR l_query;
    ???? Select count(*) into totalResults from searchResults; ????
    This last row is the issue

    Thanks heaps!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2007
    Posts
    21
    Hey thanks for the really fast reply, but it doesnt seem to work.

    Ive got:

    OPEN searchResults FOR select * from customer;

    totalResults := SQL%ROWCOUNT;

    dbms_output.put_line(TOTALRESULTS);

    But its blank???

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    1) you should read & follow the posting guidelines in the STICKY post at the top of this forum.
    2) you should use CUT & PASTE to show the whole session including what you did & how Oracle responded.
    3) if you did only what you posted, then I am NOT surprised by the results.
    4) Opening a cursor, does exactly that. It OPENS a cursor. It does NOT retrieve any rows.You need to LOOP thru the rows before it gets populated.

    Code:
      1  declare
      2  totalResults number;
      3  cnt number;
      4  cursor l_cursor is  select * from user_objects;
      5  l_rec l_cursor%ROWTYPE;
      6  begin
      7  dbms_output.enable(100000);
      8  cnt:=0;
      9  OPEN l_cursor;
     10  loop
     11     FETCH l_cursor into l_rec;
     12     EXIT WHEN l_cursor%NOTFOUND;
     13     cnt := cnt +1;
     14  end loop;
     15  totalResults := l_cursor%ROWCOUNT;
     16  dbms_output.put_line('CNT1 = ' ||CNT );
     17  dbms_output.put_line('ROW_CNT = '||totalResults);
     18* end;
    SQL> /
    CNT1 = 195
    ROW_CNT = 195
    
    PL/SQL procedure successfully completed.
    Last edited by anacedent; 07-08-07 at 23:42.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2007
    Posts
    21
    thanks anacedent, I will do so in the future, I do appreciate the help!

    I will let you know tomorrow how I go with your example.

  6. #6
    Join Date
    Jun 2007
    Posts
    21
    Hi, I ended up just doing another select count(*). It was just easier, and wont have too much of a performance hit.

    thanks for the help

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Select count(*) could bring back a different # of rows than your cursor, in a highly concurrent environment where your data is changing. The only accurate way to get a count of rows processed by a cursor is SQL%ROWCOUNT.

    ---=cf

  8. #8
    Join Date
    Jun 2007
    Posts
    21
    That is true chuck. Thanks for the info, but I thought that you could not do a SQL%ROWCOUNT on a cursor?

    Do you have any particular examples?

    Thanks!

  9. #9
    Join Date
    Jun 2007
    Posts
    21
    Just thought I would report further. I did some reading in the SQL%ROWCOUNT property and found that you can do something like this:

    open cursor_name for select id from customer
    fetch cursor_name bulk collect into id_var
    results_count := cursor_name%ROWCOUNT

    and that works quite efficiently!

    references:
    http://www.unix.org.ua/orelly/oracle...8i/ch05_04.htm

    http://www.oracle-base.com/articles/8i/BulkBinds8i.php

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    That sample code is in anacedent's post, btw.
    --=cf

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by markmacumber
    Just thought I would report further. I did some reading in the SQL%ROWCOUNT property and found that you can do something like this:

    open cursor_name for select id from customer
    fetch cursor_name bulk collect into id_var
    results_count := cursor_name%ROWCOUNT

    and that works quite efficiently!
    Quite efficiently, yes. But bear in mind that some queries could return many thousands of rows, and might take a long time about it. You can limit the amount of rows counted using the LIMIT clause:
    Code:
    open cursor_name for select id from customer;
    fetch cursor_name bulk collect into id_var LIMIT 201;
    results_count := cursor_name%ROWCOUNT;
    Now if your results_count returns 201 you can modify the information displayed to the user to something like "More than 200 rows found".

  12. #12
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    If it's OK to pull the entire resultset back (and not do any processing until you've got it), don't you store it in some sort of array in Java? And if so, can't it tell you the number of elements it has? I don't know much Java I'm afraid.

Posting Permissions

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