Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Posts
    20

    Question Unanswered: Check for Cursor size before returning

    Hi,

    How do I check the number of rows in a cursor before returning the cursor, at the moment I have code such this in my function.

    open myCursor for DML;
    RETURN myCursor;

    I would prefer not to run the DML outside of the cursor to do a row count, as I would want it to be more efficient, thus how can I tell how many rows there are in a cursor before considering whether to return (and then do alternative dml as a result).

    thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Check for Cursor size before returning

    Originally posted by kop
    Hi,

    How do I check the number of rows in a cursor before returning the cursor, at the moment I have code such this in my function.

    open myCursor for DML;
    RETURN myCursor;

    I would prefer not to run the DML outside of the cursor to do a row count, as I would want it to be more efficient, thus how can I tell how many rows there are in a cursor before considering whether to return (and then do alternative dml as a result).

    thanks!
    You can't, other than by issuing a separate COUNT query like:

    SELECT COUNT(*) FROM (the query of the cursor)

    When you open a cursor, no data is actually retrieved until you FETCH it. i.e. there are no rows "in" the cursor to count.

  3. #3
    Join Date
    Mar 2002
    Posts
    20

    Re: Check for Cursor size before returning

    thanks - i will use that:

    the other alternative i was considering until you answered was going to be: -

    test_rec dual%rowtype;
    open mCursor for DML;
    fetch myCursor into test_rec;
    if ( myCursor%notfound )
    begin
    etc etc

Posting Permissions

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