ORA-06511: PL/SQL: cursor already open Even after closing

Question

I have created a package with a procedure such that :

 PROCEDURE person_phone_data_load
   IS
      CURSOR cur_phone_info
      is
         SELECT itnpi.ROWID row_id, itnpi.*
           from xx_TAL_NEWHIRE_PHONE_INT ITNPI,
             xx_tal_newhire_employ_int emp
          where 1 = 1 and ITNPI.STATUS in ('N', 'E')
               and EMP.CANDIDATE_NUMBER=ITNPI.CANDIDATE_NUMBER
            and BUSINESS_UNIT='APGEN BUSINESS GROUP'
          ;
/**
select phone.ROWID row_id, phone.candidate_number, DECODE(phone.phone_type,'Work Phone','WORK','Home Phone','HOME','Cellular Phone','MOBILE',phone.phone_type) PHONE_TYPE,
       case phone.phone_type
           when 'Mobile Phone' then mobile_number
           when 'Work Phone'   then WORK_PHONE 
           when 'Home Phone'   then home_number
           end   as phone_number
from xx_tal_newhire_phone_int phone
 Where 1 = 1 And Phone.Status In ('N', 'E'); 
 **/
      CURSOR lcu_get_person_id (p_candidate_number VARCHAR2)
      IS
         SELECT   papf.person_id, papf.start_date
             FROM per_all_people_f papf, per_people_extra_info ppei
            WHERE papf.person_id = ppei.person_id
              AND ppei.information_type = 'XXHR_TAL_NEWHIRE_READER'
              AND ppei.pei_information1 = p_candidate_number
              AND papf.effective_start_date =
                                    (SELECT MAX (papf1.effective_start_date)
                                       FROM per_all_people_f papf1
                                      WHERE papf1.person_id = papf.person_id)
         GROUP BY papf.person_id, papf.start_date;

      CURSOR lcu_check_phone (p_personid NUMBER, p_phone_type VARCHAR2)
      IS
         SELECT phone_number
           FROM per_phones
          WHERE SYSDATE BETWEEN date_from AND NVL (date_to, '31-DEC-4712')
            AND phone_type = p_phone_type
            AND parent_id = p_personid;

      lv_phone_type              VARCHAR2 (240);
      ln_phone_type_excep        VARCHAR2 (2000);
      an_phone_type_excep        VARCHAR2 (2000);
      ln_phone_id                NUMBER;
      ln_object_version_number   NUMBER;
      ld_ph_date_from            DATE;
      ld_ph_date_to              DATE;
      lv_phone_number            VARCHAR2 (100);    

      ln_person_id               NUMBER;
      ln_phone_err_msg           VARCHAR2 (2000);
      ln_person_excep            VARCHAR2 (2000);
      an_person_excep            VARCHAR2 (2000);
      lc_rec_status              VARCHAR2 (1);
      ln_phoneno                 NUMBER;
      ln_start_date               DATE;
   BEGIN
      fnd_file.put_line (fnd_file.LOG,
                         '**********************************************'
                        );
      fnd_file.put_line (fnd_file.LOG,
                         '** New Hire Phone load Program Starts **'
                        );

      FOR phone_info_rec IN cur_phone_info
      LOOP
         BEGIN
            lv_phone_type := NULL;
            ln_phone_id := NULL;
            ln_object_version_number := NULL;
            ln_person_id := NULL;
            ln_phone_err_msg := NULL;
            ld_ph_date_from := TRUNC (SYSDATE);
            ld_ph_date_to := NULL;
            lv_phone_number := phone_info_rec.phone_number ;

            ln_phone_type_excep := NULL;
            ln_person_excep := NULL;
            lc_rec_status := 'S';

            IF phone_info_rec.phone_type IS NOT NULL
            THEN
               lv_phone_type :=
                     get_lookup_code ('PHONE_TYPE', phone_info_rec.phone_type);

               IF lv_phone_type = 'NO_VALUE'
               THEN
                  lc_rec_status := 'E';
                  -- Could not decode the SEX for the Employee.
                  ln_phone_err_msg :=
                        ln_phone_err_msg
                     || 'Invalid Phone Type for candidate_number: '
                     || phone_info_rec.candidate_number
                     || ' # ';
               END IF;
            END IF;

            IF phone_info_rec.candidate_number IS NOT NULL
            THEN
               ln_person_id := NULL;

               OPEN lcu_get_person_id (phone_info_rec.candidate_number);

               FETCH lcu_get_person_id
                INTO ln_person_id, ln_start_date;

               CLOSE lcu_get_person_id;

               IF ln_person_id IS NULL
               THEN
                  lc_rec_status := 'E';
                  -- Employee Not Created for the Candidate Number.
                  ln_phone_err_msg :=
                        ln_phone_err_msg
                     || 'Employee Record not created for the candidate_number: '
                     || phone_info_rec.candidate_number
                     || ' # ';
               ELSIF ln_person_id IS NOT NULL
               THEN
                  OPEN lcu_check_phone (ln_person_id, lv_phone_type);

                  FETCH lcu_check_phone
                   Into Ln_Phoneno;
   Close lcu_check_phone;

         If Lcu_Check_Phone%Isopen Then
                        Close Lcu_Check_Phone;
                        END IF; 
                  IF ln_phoneno = phone_info_rec.phone_number
                  Then
                     lc_rec_status := 'P';
                     ln_phone_err_msg :=
                           ln_phone_err_msg
                        || 'Phone already exists for the candidate_number:  '
                        || phone_info_rec.candidate_number
                        || ' # ';


                  END IF;


               END IF;
            ELSE
               lc_rec_status := 'E';
               -- Candidate Number is NULL.
               ln_phone_err_msg :=
                     ln_phone_err_msg
                  || 'Candidate Number is NULL; So Could not find the Employee for Address creation';
            END IF;

            IF lc_rec_status = 'S'
            THEN
               BEGIN

                  UPDATE xx_tal_newhire_phone_int iph
                     SET status = 'P',
                         error_msg = NULL
                   WHERE iph.ROWID = phone_info_rec.row_id;

                  COMMIT;
                  fnd_file.put_line
                     (fnd_file.LOG,
                         'Successfully Processed phone record for candidate_number: '
                      || phone_info_rec.candidate_number
                     );
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     ln_phone_err_msg :=
                           'Phone API Error while processing candidate_number: '
                        || phone_info_rec.candidate_number
                        || ' , '
                        || SUBSTR (SQLERRM, 1, 150);

                     UPDATE xx_tal_newhire_phone_int iph
                        SET status = 'E',
                            error_msg = ln_phone_err_msg
                      WHERE iph.ROWID = phone_info_rec.row_id;

                     fnd_file.put_line
                        (fnd_file.LOG,
                            'Person API Error while processing candidate_number: '
                         || phone_info_rec.candidate_number
                         || ' , '
                         || SUBSTR (SQLERRM, 1, 150)||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
                        );
               END;
            ELSE
               UPDATE xx_tal_newhire_phone_int iph
                  SET status = lc_rec_status,
                      error_msg = ln_phone_err_msg
                WHERE iph.ROWID = phone_info_rec.row_id;
            END IF;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               ln_phone_err_msg :=
                                 'Phone Error : ' || SUBSTR (SQLERRM, 1, 150)||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();

               Update xx_Tal_Newhire_Phone_Int Iph
                  SET status = 'E',
                      error_msg = ln_phone_err_msg
                WHERE iph.ROWID = phone_info_rec.row_id;

               COMMIT;
         END;
      End Loop;

      fnd_file.put_line (fnd_file.LOG,
                         '** New Hire Phone load Program Ends **'
                        );
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,
                            'In phone main :' || SUBSTR (SQLERRM, 1, 150)
                           );
         ROLLBACK;
   END person_phone_data_load;

I am getting a :"Phone Error : ORA-06511: PL/SQL: cursor already open

error in the log.This is for teh cursor lcu_check_phone. I also explicitly added an if statement to close the cursor if open. Still i am getting this error.

Answer 1

To avoid open/close/fetch cursor problems use cursor loops whenever it is possible. You dont have to worry about fetching all values from cursor in one fetch and about loop counts as it is controlled itself by loop ( like for:each loops from other languages). I use standard open/fetch/close usually for check does the cursor load some data, when dont it will set the %NOTFOUND flag to true which is usefull for example in error handling in opposite to select into which throws NO_DATA_FOUND when no data were selected.

Answer 2

I think the problem you have is this

              OPEN lcu_check_phone (ln_person_id, lv_phone_type);

              FETCH lcu_check_phone
               Into Ln_Phoneno;
              Close lcu_check_phone;

Phone number is not a numeric field - but Ln_Phoneno is defined as a number. Your fetch statement is throwing an exception that is handled, but this bypasses both of your close statements. Then in the next iteration you are unable to re-open the cursor.

If you're going to have all these nested loops then you need to put close statements in your exception handlers.

The alternative is to be more modular - use a number of small procedures to break up your code - then you don't have to worry about scope so much since each cursor will only exist for the duration of a single iteration. If that makes sense...