PL/SQL cursor question

I am trying to correct this PL/SQL code (9i database) to update correctly.  I am getting the error ORA-01427: single-row subquery returns more than one row because there can be the same value for rcrapp3_yr_in_coll_2 for different rcrapp1_pidms/rcrapp3_pidms.

UPDATE rprlcrt
   SET rprlcrt_clas_code =
          (SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0')
 WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;
*********************************************************************************************************************************
I guess I need a cursor to cycle thru the table RPRLCRT for each ID and update with the nested subquery.  Here is my attempt to write a cursor.  Please advise

CREATE OR REPLACE PROCEDURE update_loans_1
IS
  v_rprlcrt_pidm  NUMBER;
  CURSOR c_select_pidm
  IS
     SELECT rprlcrt_pidm
      FROM RPRLCRT
     WHERE rprlcrt_cint_code IS NULL AND rprlcrt_cert_date IS NULL;

BEGIN
    OPEN c_select_pidm;

    LOOP
       FETCH c_select_pidm
          INTO v_rprlcrt_pidm;
            SELECT rcrapp3_yr_in_coll_2
                      FROM rcrapp3, rcrapp1
                     WHERE rcrapp1_curr_rec_ind = 'Y'
                       AND RCRAPP1_INFC_CODE = 'EDE'
                       AND RCRAPP1_INFC_CODE = RCRAPP3_INFC_CODE
                       AND RCRAPP1_SEQ_NO = RCRAPP3_SEQ_NO
                       AND rcrapp1_aidy_code = '0708'
                       AND rcrapp1_pidm = rcrapp3_pidm
                       AND rcrapp3_aidy_code = '0708'
                       AND rcrapp3_yr_in_coll_2 <> '0'
                       AND rcrapp1_pidm = v_rprlcrt_pidm;
                       COMMIT;
            EXIT WHEN c_select_pidm%NOTFOUND;
     END LOOP;
END;




Start Free Trial