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
DECLARE
v_strTransNo VARCHAR2(30);
CURSOR ResultsCur IS
SELECT OrderID from Invoice where Invoice ID = '1234'
ResultsRec :g_ResultsCur%ROWTYPE;
BEGIN
OPEN ResultsCur;
LOOP
FETCH ResultsCur INTO ResultsRec;
EXIT WHEN ResultsCur%NOTFOUND;
:g_Count := ResultsCur%ROWCOUNT;
END LOOP;
CLOSE ResultsCur;
IF :g_Count > 5 then
OPEN g_ResultsCur FOR Select 'Too many Orders for one Invoice'
from dual;
DBMS_OUTPUT.PUT_LINE('There are '||:g_Count||' Order records');
ELSE
OPEN :g_ResultsCur FOR
SELECT OrderID from Invoice where Invoice ID = '1234';
END IF;
END;
/
Print :g_ResultsCur
/
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.