title: Generating dates between 2 given dates and looping in PL/SQL
body: <p>I am very new to PL/SQL. I have created a table in Oracle database and based on that table, I have to generate a report to show the number of open bugs from 5/1/2013 through 5/31/2013, and at the end of the report, need to display the maximum number of open bugs on a single day (using PL/SQL anonymous block).
  Table definition and data is below:</p>
  
  <pre><code>CREATE TABLE bugs
  (
  BUG_ID          NUMBER PRIMARY KEY,
  REPORTED_DATE   DATE NOT NULL,
  DESCRIPTION     VARCHAR2(20),
  PRIORITY        NUMBER(2),
  ASSIGNED_TO     VARCHAR2(10),
  CLOSED_DATE     DATE,
  NOTE            VARCHAR2(20)
  );
  
  INSERT INTO BUGS VALUES (1230, '25-APR-13', NULL, 3, 'Team 3', '28-APR-13', NULL); 
  INSERT INTO BUGS VALUES (1231, '29-APR-13', NULL, 1, 'Team 1', '29-APR-13', NULL); 
  INSERT INTO BUGS VALUES (1232, '03-MAY-13', NULL, 1, 'Team 1', '03-MAY-13', NULL); 
  INSERT INTO BUGS VALUES (1233, '03-MAY-13', NULL, 1, 'Team 3', '08-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1234, '04-MAY-13', NULL, 2, 'Team 5', '15-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1235, '04-MAY-13', NULL, 2, 'Team 1',  NULL,       NULL);
  INSERT INTO BUGS VALUES (1236, '05-MAY-13', NULL, 1, 'Team 2', '06-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1237, '05-MAY-13', NULL, 3, 'Team 3', '10-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1238, '09-MAY-13', NULL, 4, 'Team 5', '16-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1239, '09-MAY-13', NULL, 5, 'Team 6',  NULL,       NULL);
  INSERT INTO BUGS VALUES (1240, '12-MAY-13', NULL, 5, 'Team 2', '30-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1241, '12-MAY-13', NULL, 1, 'Team 1', '20-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1242, '13-MAY-13', NULL, 4, 'Team 4', '25-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1243, '14-MAY-13', NULL, 4, 'Team 3', '01-JUN-13', NULL);
  INSERT INTO BUGS VALUES (1244, '14-MAY-13', NULL, 2, 'Team 4', '25-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1245, '20-MAY-13', NULL, 2, 'Team 4',  NULL,       NULL);
  INSERT INTO BUGS VALUES (1246, '22-MAY-13', NULL, 2, 'Team 4', '25-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1247, '25-MAY-13', NULL, 2, 'Team 1', '29-MAY-13', NULL);
  INSERT INTO BUGS VALUES (1248, '30-MAY-13', NULL, 1, 'Team 1', '01-JUN-13', NULL);
  INSERT INTO BUGS VALUES (1249, '05-JUN-13', NULL, 1, 'Team 2', '07-JUN-13', NULL);
  COMMIT;
  </code></pre>
  
  <p>“Open Bugs” - A bug is considered open on a given day if (1) its “REPORTED_DATE” is on or before that day, and (2) its “CLOSED_DATE” is on or after that day (or is unknown (NULL)). For example, we have 5 open bugs on 5/5/2013.</p>
  
  <p>The output of the program should look like the following:</p>
  
  <pre><code>Date      Number of Open Bugs   
  01-MAY-13            0
  02-MAY-13            0
  03-MAY-13            2  
  04-MAY-13            3
  05-MAY-13            5
  06-MAY-13            5
  07-MAY-13            4
  08-MAY-13            4
  ---------            --
  ---------            --
  
  The maximum number of open bugs on a single day is 9.
  There were 9 open bugs on 14-MAY-13.
  There were 9 open bugs on 15-MAY-13.
  There were 9 open bugs on 25-MAY-13.
  </code></pre>
  
  <p>I have searched and found that there is ADD_MONTHS and CONNECT_BY_LEVEL functions that I can use, to generate the list of days in the month and then use a loop to count the open bugs on that particular day of the month, but do not know how to exactly use them. Can someone please help me get started. Thanks in advance.</p>