I want a rwocount of an implicit query in a FOR LOOP.
DECLARE
Cow PLS_INTEGER;
BEGIN
FOR Moo IN (SELECT Dummy FROM Dual CONNECT BY LEVEL <= 5)
LOOP
NULL;
END LOOP;
DBMS_OUTPUT.PUT_LINE(Moo%ROWCOUNT);
END;
/
Results in: PLS-00324: cursor attribute may not be applied to non-cursor 'MOO'
This is beside the reference to a variable outside of its scope.
Assuming i have to do it the old fashioned way, i am thinking of two methods:
1) Put a counter in the LOOP:
DECLARE
Cow PLS_INTEGER := 0;
BEGIN
FOR Moo IN (SELECT Dummy FROM Dual CONNECT BY LEVEL <= 5)
LOOP
Cow := Cow + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(Cow);
END;
/
2) Retrieve ROWNUM, rewriting it each time.
DECLARE
Cow PLS_INTEGER := 0;
BEGIN
FOR Moo IN (SELECT ROWNUM RN, Dummy FROM Dual CONNECT BY LEVEL <= 5)
LOOP
Cow := Moo.RN;
END LOOP;
DBMS_OUTPUT.PUT_LINE(Cow);
END;
/
To me, option 1 is clear, option 2 is cute. Is there another method?