How can I find the number of records in an Oracle PL/SQL cursor?

Question

Here's my cursor:

CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;

I immediately open the cursor in order to lock these records for the duration of my procedure.

I want to raise an application error in the event that there are < 2 records in my cursor. Using the C1%ROWCOUNT property fails because it only counts the number which have been fetched thus far.

What is the best pattern for this use case? Do I need to create a dummy MY_TABLE%ROWTYPE variable and then loop through the cursor to fetch them out and keep a count, or is there a simpler way? If this is the way to do it, will fetching all rows in my cursor implicitly close it, thus unlocking those rows, or will it stay open until I explicitly close it even if I've fetched them all?

I need to make sure the cursor stays open for a variety of other tasks beyond this count.

Answer 1

Create a savepoint before you iterate through the cursor and then use a partial rollback when you find there are < 2 records returned.

Answer 2

You can start transaction and check if SELECT COUNT(*) MY_TABLE WHERE SALARY < 50000 greater than 1.

Answer 3

If you're looking to fail whenver you have more than 1 row returned, try this:

declare 
  l_my_table_rec my_table%rowtype;
begin
  begin
    select * 
      from my_table
      into l_my_table_rec
     where salary < 50000
       for update;
  exception
    when too_many_rows then
      -- handle the exception where more than one row is returned
    when no_data_found then
      -- handle the exception where no rows are returned
    when others then raise;
  end;

  -- processing logic
end;

Answer 4

If this is the way to do it, will fetching all rows in my cursor implicitly close it, thus unlocking those rows

The locks will be present for the duration of the transaction (ie until you do a commit or rollback) irrespective of when (or whether) you close the cursor.

I'd go for

declare
  CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;;
  v_1 c1%rowtype;
  v_cnt number;
begin
  open c_1;
  select count(*) into v_cnt FROM MY_TABLE WHERE SALARY < 50000 and rownum < 3;
  if v_cnt < 2 then
    raise_application_error(-20001,'...');
  end if;
  --other processing
  close c_1;
end;

There's a very small chance that, between the time the cursor is opened (locking rows) and the select count, someone inserts one or more rows into the table with a salary under 50000. In that case the application error would be raised but the cursor would only process the rows present when the cursor was opened. If that is a worry, at the end do another check on c_1%rowcount and, if that problem was experienced, you'd need to rollback to a savepoint.

Answer 5

NB: i just reread you question.. and you want to fail if there is ONLY 1 record.. i'll post a new update in a moment..

lets start here..

From Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 reference

All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. Since the rows are no longer locked, you cannot fetch from a FOR UPDATE cursor after a commit.

so you do not need to worry about the records unlocking.

so try this..

declare 
  CURSOR mytable_cur IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;

  TYPE mytable_tt IS TABLE OF mytable_cur %ROWTYPE
    INDEX BY PLS_INTEGER;

  l_my_table_recs mytable_tt;
  l_totalcount NUMBER;
begin

   OPEN mytable_cur ;
   l_totalcount := 0;

   LOOP
      FETCH mytable_cur 
      BULK COLLECT INTO l_my_table_recs LIMIT 100;

      l_totalcount := l_totalcount + NVL(l_my_table_recs.COUNT,0);

      --this is the check for only 1 row..
      EXIT WHEN l_totalcount < 2;

      FOR indx IN 1 .. l_my_table_recs.COUNT
      LOOP
         --process each record.. via l_my_table_recs (indx)

      END LOOP;

      EXIT WHEN mytable_cur%NOTFOUND;
   END LOOP;

   CLOSE mytable_cur ;
end;

---

ALTERNATE ANSWER I read you answer backwards to start and thought you wanted to exit if there was MORE then 1 row.. not exactly one.. so here is my previous answer.

2 simple ways to check for ONLY 1 record.

Option 1 - Explicit Fetchs

declare 
  CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;
  l_my_table_rec C1%rowtype;
  l_my_table_rec2 C1%rowtype;
begin

    open C1;
    fetch c1 into l_my_table_rec;

    if c1%NOTFOUND then
       --no data found
    end if;

    fetch c1 into l_my_table_rec2;
    if c1%FOUND THEN
      --i have more then 1 row
    end if;
    close c1;

  -- processing logic

end;

I hope you get the idea.

Option 2 - Exception Catching

declare 
  CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE;
  l_my_table_rec C1%rowtype;
begin
  begin
    select * 
      from my_table
      into l_my_table_rec
     where salary < 50000
       for update;
  exception
    when too_many_rows then
      -- handle the exception where more than one row is returned
    when no_data_found then
      -- handle the exception where no rows are returned
    when others then raise;
  end;

  -- processing logic
end;

Additionally Remember: with an explicit cursor.. you can %TYPE your variable off the cursor record rather then the original table.

this is especially useful when you have joins in your query.

Also, rememebr you can update the rows in the table with an

UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;

type statement, but I that will only work if you haven't 'fetched' the 2nd row..

for some more information about cursor FOR loops.. try Here