title: PL/SQL: ORA-44201: cursor needs to be reparsed
body: <p>I have the following sql code that executes successfully in TOAD (without the INTO statement). However, when I insert the code in an sql program (with the INTO) and compile, the "from C" line is highlighted, and I get the ORA-44201 error: cursor needs to be reparsed.  I'm a noob, so I'm clueless, and my DBA buddies have never seen this error.  Any thoughts?</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE Distribute_CA_Accounts 
  IS
  BEGIN
      /* This procedure replaces RM103r */
      DECLARE
          /* Output File Variables */
          file_location      VARCHAR2 ( 60 ) := 'WCIS_OUTPUT';
          file_name          VARCHAR2 ( 60 ) := 'UEF_Assign.csv';
          vOutHandle         UTL_FILE.file_type;
          sep                VARCHAR2 ( 1 ) := ',';
          header_line        VARCHAR2 ( 500 );
          /* Processing Variables */
          old_ca             VARCHAR2 ( 5 );
          old_ca_name        VARCHAR2 ( 30 )  := 'UNASSIGNED';
          new_ca             VARCHAR2 ( 5 );
          new_ca_name        VARCHAR2 ( 30 );
          volunteer_ca       VARCHAR2 ( 5 ) := 'D743';
          ca_code_in         VARCHAR  ( 5 );
          uef_pct            NUMBER;
          ca_name            VARCHAR  ( 20 );
          ca_code            VARCHAR  ( 5 );
          clm_count          VARCHAR  ( 5 );
          calc_code          VARCHAR  ( 5 );
          calc_name          VARCHAR  ( 40 );
          calc_diff          NUMBER;
          calc_uef_pct       NUMBER;
          calc_pct           NUMBER;
          /* Email Variables */
          email_list         VARCHAR2 ( 1 );
          output_line        VARCHAR2 ( 500 );
          mailed_from        VARCHAR2 ( 500 ) := 'oicwvaccisa@wv.gov';
          send_mail_to       VARCHAR2 ( 500 );
          email_subject      VARCHAR2 ( 60 );
          email_message      VARCHAR2 ( 500 );
          /*    individual_id        VARCHAR2(5); */
          individual_address VARCHAR2 ( 60 );
  
  
          /* Pulls accounts for main procedure */
          /* THIS CODE EXCLUDES RSLVD ACCOUNTS */
          CURSOR get_unassigned_recs
          IS
              SELECT
               policy_num account_num,
               bus_seq_num account_seq,
               cm_unit_code b_code,
               NVL ( feid_num, '11-1111111' ) feid_num,
               NVL ( manual_class, '9999' ) manual_class
              FROM
               business B
              WHERE
                  fund_type_code IN ( 'P',
                                      'U',
                                      'N') AND
                  collection_status_code IN ('UNINS',
                                             'COVRD',
                                             'INSUR',
                                             'DEFLT',
                                             'INJ',
                                             'UNCOL',
                                             'PEND',
                                             'PEHRG' ) AND
                  cm_unit_code IS NULL;
  
          TYPE unarray IS TABLE OF get_unassigned_recs%ROWTYPE;
  
          curarray1          unarray;
  
  
          /* Pulls list of users for distribution of report  */
          /* Pulls list of users for distribution of report  */
          CURSOR get_email_group
          IS
              SELECT
               email_addr
              FROM
               report_dist
              WHERE
               so_module = 'RM103R' AND
               email_addr IS NOT NULL;
  
          TYPE uarray IS TABLE OF get_email_group%ROWTYPE;
  
          curarray2          uarray;
  
          BEGIN
  
              /* sets up output file  */
              header_line      :=
                  'Account #' ||
                  sep ||
                  'New CA' ||
                  sep ||
                  'New CA Name';
  
              vOutHandle      :=
                  UTL_FILE.fopen ( file_location, file_name, 'w' );
              UTL_FILE.put_line ( vOutHandle, header_line );
  
              /* sets up email */
              email_subject := 'CA Assign';
              email_message      :=
                      'Attached is list of unassigned accounts ' ||
                      'assigned a Credit Analyst : ';
  
              email_message      :=
                  email_message ||
                  CHR ( 13 )||
                  CHR ( 10 )||
                  '   ' ||
                  CHR ( 13 )||
                  CHR ( 10 )||
                  CHR ( 13 )||
                  CHR ( 10 )||
                  'If there is a problem please notify IT.  Thank you.';
  
                  /* This is the main procedure */
  
              OPEN get_unassigned_recs;
  
              LOOP
                  FETCH get_unassigned_recs
                      BULK
                             COLLECT INTO curarray1;
  
                  FOR i IN 1 .. curarray1.COUNT
                  LOOP
  
                      IF ( curarray1 ( i ).manual_class = '8840' OR
                          curarray1 ( i ).manual_class = '7707' OR
                          curarray1 ( i ).manual_class = '7711' AND
                          ( curarray1 ( i ).feid_num NOT IN ('11-1111111', '111-11-1111', '00-0000000', '000-00-0000')))
                      THEN    
                              new_ca      := volunteer_ca;
                              new_ca_name := 'DWHITE2';
                      ELSE
              /* CALL ALALYTICS */
  
                      WITH C AS (SELECT
                                  b.cm_unit_code,
                                  NVL(c.uef_percentage, 0) as uef_pct,
                                  c.user_name, 
                                  ca_name,
                                  count(*) as clm_count
  
                              FROM business b right outer join cm_unit_code c
                                  on c.CODE = b.CM_UNIT_CODE
                              WHERE
                                  b.cm_unit_code NOT IN ('9747',
                                                         'BANK',
                                                         '9999',
                                                         'SELF') AND
                                  b.fund_type_code IN ('P', 
                                                       'U',
                                                       'N') AND
                                  b.collection_status_code IN ('UNINS', 
                                                               'COVRD',
                                                               'INSUR',
                                                               'DEFLT',
                                                               'INJ',
                                                               'UNCOL',
                                                               'PEND',
                                                               'PEHRG') AND
                                  c.uef_percentage &gt; 0
                                  group by b.cm_unit_code, c.user_name, c.UEF_PERCENTAGE)
                          SELECT cm_unit_code ca_code, 
                                 uef_pct - (clm_count/ sum (clm_count) over ())*100, 
                                 uef_pct, 
                                 (clm_count/ sum (clm_count) over ())*100,
                                 ca_name
                          INTO
                                 calc_code,
                                 calc_diff,
                                 calc_uef_pct,
                                 calc_pct,
                                 calc_name
                          from C
                          order by 2 desc
                          FETCH FIRST ROW ONLY;  
  
                          new_ca      := calc_code;
                          new_ca_name := calc_name;
  
                      /*  new_ca      := ca_code;
                          new_ca_name := ca_name;  */
  
  
                      END IF; 
  
  
  
                          output_line      :=
                                  curarray1 ( i ).account_num ||
                                  '-' ||
                                  curarray1 ( i ).account_seq ||
                                  sep ||
                                  new_ca ||
                                  sep ||
                                  new_ca_name;
  
                              UTL_FILE.put_line ( vOutHandle, output_line );
  
                              UPDATE
                               business
                              SET
                               cm_unit_code = new_ca
                              WHERE
                               policy_num = curarray1 ( i ).account_num AND
                               bus_seq_num = curarray1 ( i ).account_seq;
  
                              INSERT INTO
                               endorsement (
                                   endorse_seq_num,
                                   policy_num,
                                   bus_seq_num,
                                   endorse_code,
                                   endorse_date,
                                   old_value,
                                   new_value,
                                   user_name
                               )
                              VALUES
                               (
                                   endorsement_seq.NEXTVAL,
                                   curarray1 ( i ).account_num,
                                   curarray1 ( i ).account_seq,
                                   '751',
                                   TRUNC ( SYSDATE ),
                                   old_ca,
                                   new_ca,
                                   'WVACCISA'
                               );
                  END LOOP;
                  OPEN get_email_group;
  
                  LOOP
                      FETCH get_email_group
                          BULK
                                 COLLECT INTO curarray2;
  
                      FOR i IN 1 .. curarray2.COUNT
                      LOOP
                          IF ( send_mail_to IS NULL ) THEN
                              send_mail_to := curarray2 ( i ).email_addr;
                          ELSE
                              send_mail_to      :=
                                  send_mail_to ||
                                  ',' ||
                                  curarray2 ( i ).email_addr;
                          END IF;
                      END LOOP;
  
                      EXIT WHEN curarray2.COUNT = 0;
                  END LOOP;
  
                  CLOSE get_email_group;
  
                  send_email.send ( ToList     =&gt; send_mail_to,
                                    Subject    =&gt; email_subject,
                                    Body       =&gt; email_message,
                                    FromEmail  =&gt; mailed_from,
                                    AttachList =&gt; file_name,
                                    Directory  =&gt; file_location
                                   );
  
                  EXIT WHEN curarray1.COUNT = 0;
              END LOOP;
  
              CLOSE get_unassigned_recs;
  
             /* COMMIT; */
             ROLLBACK;
  
               UTL_FILE.FCLOSE_ALL;
  
  
  
      END;
  END Distribute_CA_Accounts;
  </code></pre>