Using ROWCOUNT Attribute in REFCUR Cursor variable
Hi I have a script which returns a SELECT staement into a Bind variable REFCUR as I want to be able to display the results in SQLPlus using PRINT.
VARIABLE g_ResultsCur REFCURSOR
Variable g_Count NUMBER
CURSOR ResultsCur IS
SELECT OrderID from Invoice where Invoice ID = '1234'
FETCH ResultsCur INTO ResultsRec;
EXIT WHEN ResultsCur%NOTFOUND;
:g_Count := ResultsCur%ROWCOUNT;
IF :g_Count > 5 then
OPEN g_ResultsCur FOR Select 'Too many Orders for one Invoice'
DBMS_OUTPUT.PUT_LINE('There are '||:g_Count||' Order records');
OPEN :g_ResultsCur FOR
SELECT OrderID from Invoice where Invoice ID = '1234';
The trouble is if the result set from the cursor returned more than 5 rows than this violates a business rule and I want to be able to flag that up. Is it possible to use Cursor%ROWCOUNT to determine rows fetched ?
Any help would be great.