Cursor to return multiple rows

Question

Hello experts, I've been working on this for the past few days and I can't find my way out of it. I have a C# web application that needs to print reports on screen. This same application calls a stored procedure on the database (Oracle) and it returns a cursor. Here is the procedure

PROCEDURE report_total(beginDate IN DATE, endDate IN DATE, c OUT REF CURSOR)
AS
BEGIN
   OPEN
      c
   FOR
      SELECT
          month
          ,sum(field1)
          -- + a lot of other fields
      FROM
          view1 v
      WHERE
          beginDate <= v.date
          AND v.Date < endDate
      GROUP BY
          month
END;

This works fine, it gives me the sum of field1 (and others) per months. Assuming you enter, at least, a complete year range you'll get, at most, 12 rows. Howhever. I would like to make something a similar stored procedure that would give me the detail of these months.

Let's say beginDate = '2003-01-01' and the endDate = '2005-01-05' (YYYY-MM-DD), I would need 25 rows. One per month, per year. And I would like to get these results with the cursor OUT. If you have a simplest idea that wouldn't involve a cursor please suggest me.

Answer 1

SELECT  TRUNC(v.date, 'MONTH'), SUM(field1)
FROM    view1 v
WHERE   v.date BETWEEN beginDate and endDate
GROUP BY
        TRUNC(v.date, 'MONTH')