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.
Best Answer
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
so you do not need to worry about the records unlocking.
so try this..
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
I hope you get the idea.
Option 2 - Exception Catching
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
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