SQL & PL/SQL

Good morning, guys.

I am having some trouble with a procedure in Oracle 9.2.0.1.0.

I have a query that returns many rows, but whenever I try to iterate through it using a foreach loop, I get only the first row and then the loop is finished.

For instance, here is the query:

select 
      (
        CASE
          WHEN
            TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'),'DD/MM/YYYY') < DATE '2009-01-01'
          THEN DATE '2009-01-01'
          ELSE
            TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'), 'DD/MM/YYYY')
          END
      ) startdate,


      (
        CASE
          WHEN
          TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level) -1,'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'),'DD/MM/YYYY') > DATE '2009-06-08'
          THEN DATE '2009-06-08'
          ELSE 
          TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level) -1,'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'), 'DD/MM/YYYY')
          END
      ) enddate
     
    FROM dual
    CONNECT by trunc(DATE '2009-06-08','dd') >= add_months(trunc(DATE '2009-01-01','dd'),level - 1)

Which shows me this output:

But whenever I run this code:

  for rec in
  (
    select 
      (
        CASE
          WHEN
            TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'),'DD/MM/YYYY') < DATE '2009-01-01'
          THEN DATE '2009-01-01'
          ELSE
            TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'), 'DD/MM/YYYY')
          END
      ) startdate,
      (
        CASE
          WHEN
          TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level) -1,'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'),'DD/MM/YYYY') > DATE '2009-06-08'
          THEN DATE '2009-06-08'
          ELSE 
          TO_DATE(
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level) -1,'dd') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'mm') || '/' ||
            to_char(add_months(trunc(DATE '2009-01-01','mm'),level - 1),'yyyy'), 'DD/MM/YYYY')
          END
      ) enddate
     
    FROM dual
    CONNECT by trunc(DATE '2009-06-08','dd') >= add_months(trunc(DATE '2009-01-01','dd'),level - 1)    
  )
  loop
    DBMS_OUTPUT.put_line(TO_CHAR(rec.startdate, 'YYYYMMDD') || '-' || TO_CHAR(rec.enddate, 'YYYYMMDD'));
  end loop;

I get this output:

20090101-20090131

I tried debugging and I verified that the loop runs only once, then skips to end loop. Am I doing something wrong?

Thank you!

Best regards,

Vinicius