Hi All,
It's really great to see every example under the sun found by google using DBMS_OUTPUT.PUTLINE() as the sole action taken on the results of each iteration of a loop, not too useful beyond the dev stage, though.
What I want is to simply report on the results of the LOOP below.
Each line sent to DBMS_OUTPUT should instead be in some form where I can point a single REF CURSOR to all of them. I simply need to output every line to a report on an ASP.NET web server. I want to do this the most efficient way possible. If that means inserting into a temp table, then selecting that resultset into a ref cursor, so be it. If not, please enlighten me. Thanks
DECLARE
vGroup NUMBER := 1;
vIter NUMBER :=1;
CURSOR cur IS
SELECT LID, SID, Cnt, Limits, Iter
FROM (
SELECT LID, SID, COUNT(*) OVER (PARTITION BY LID) Cnt, TRUNC(COUNT(*) OVER (PARTITION BY LID)/4)*4 Limits,
row_number() over (partition by LID order by SID desc) iter
FROM VW_TA
WHERE C0 > 0 AND C1 > 0 AND C2 > 0 AND C3 > 0 ORDER BY LID, SID DESC
) a WHERE ITER <= LIMITS;
BEGIN
FOR aRec IN cur
LOOP
DBMS_OUTPUT.PUT_LINE(aRec.LID || ', ' || aRec.SID || ', ' || aRec.Cnt || ', ' || aRec.Limits || ', ' || vGroup || ', ' || vIter);
IF (vIter = 4) THEN
vIter := 1;
vGroup := vGroup + 1;
ELSE
vIter := vIter +1;
END IF;
END LOOP;
END;
How 2 point ref cursor to FOR Cursor loop results
