SQL & PL/SQL

Hello all,

I'm having big performance issues with (as you'll see below, a very simple) stored procedure. Any hints/suggestions would be really appreciated. Query below in the Loop is used to create a list of all months that fall within a member's start_date & end_date for insertion to member_months table. Can anyone suggest maybe a different approach to improve run time? It was timing out just using SQL, thought I might try PL. Thanks for anyone's help.

CREATE OR REPLACE PROCEDURE proc_MEMBER_MONTHS authid current_user as 
/* get all Master member id's for cursor */
CURSOR c_unique_mmi IS
    select distinct master_member_id
    from member;

v_mmi                           member.master_member_id%TYPE; 

BEGIN

dbms_output.enable(null);

   OPEN c_unique_mmi;
    LOOP 
        FETCH c_unique_mmi INTO v_mmi;   /* pass mmi in cursor to variable */
        EXIT WHEN c_unique_mmi%NOTFOUND;
                       
  INSERT INTO member_months    
      (mmi,                                         
      member_nbr,
      lob,
      member_month,
      member_year,
      member_month_count) 

(SELECT master_member_id mmi,
        member_nbr,
        lob,
        month member_month,
        year member_year,
        ROW_NUMBER ()
          OVER (PARTITION BY member_nbr ORDER BY lob, year, month ASC)
          member_month_count
          
  FROM (SELECT DISTINCT
                   master_member_id,
                   member_nbr,
                   lob,
                   TO_CHAR (ADD_MONTHS (eligibility_start_date, LEVEL - 1), 'MM')
                      as MONTH,
                   TO_CHAR (ADD_MONTHS (eligibility_start_date, LEVEL - 1),
                            'YYYY')
                      as YEAR
              FROM (SELECT *
                      FROM mmi_data
                     WHERE master_member_id = v_mmi)              /* v_mmi is current MMI variable passed from cursor */
        CONNECT BY LEVEL <=
                        MONTHS_BETWEEN (TRUNC (eligibility_end_date, 'MM'),
                                        TRUNC (eligibility_start_date, 'MM'))
                      + 1));

    commit;
    END LOOP;
    CLOSE c_unique_mmi;
END;
/

Edited by: BluShadow on 08-Aug-2012 14:03
added {noformat}

{noformat} tags for readability.  Please read {message:id=9360002} and learn to do this yourself.