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.