DECLARE
V_RINGGOLD_ID;
V_RINGGOLD_NAME;
V_RINGGOLD_SUBJECT;
V_RINGGOLD_SUBJECT_MAPPING;
V_PQ_SUBJECT;
CURSOR RG_SUBJ
IS
SELECT DISTINCT RINGGOLD_ID FROM PQINF.RINGGOLD_IDENTIFY_SUBJECTS;

CURSOR PQ_SUBJ
IS 
 WITH
      ringgold as (
         select RINGGOLD_ID, RINGGOLD_NAME,
                to_char(regexp_substr(SUBJECTS, '[^,]+', 1, column_value)) RINGGOLD_SUBJECT
         from (select * from PQINF.RINGGOLD_IDENTIFY_SUBJECTS where ringgold_id=RG_SUBJ.RINGGOLD_ID),
             table(cast(multiset(select level from dual
                                 connect by level <= regexp_count(SUBJECTS,',')+1)
                    as sys.odciNumberList))
         where rownum > 0
     )
 SELECT  a.RINGGOLD_ID, a.RINGGOLD_NAME,a.RINGGOLD_SUBJECT,z.ringgold_subject ringgold_subject_mapping,z.PQ_SUBJECT
 FROM ringgold a, PQINF.PQ_GOLD_SUBJECTS z
 WHERE a.ringgold_subject=z.ringgold_subject(+)
 ORDER by 1, 2, 3, 4;
 BEGIN 
 OPEN PQ_SUBJ;
 LOOP
 FETCH PQ_SUBJ INTO V_RINGGOLD_ID,V_RINGGOLD_NAME,V_RINGGOLD_SUBJECT,V_RINGGOLD_SUBJECT_MAPPING,V_PQ_SUBJECT;
 EXIT 
 WHEN PQ_SUBJ%NOTFOUND;
 INSERT INTO PQINF.PQ_RINGGOLD_MAPPING(RINGGOLD_ID,RINGGOLD_NAME,RINGGOLD_SUBJECT,RINGGOLD_SUBJECT_MAPPING,PQ_SUBJECT)
 END LOOP;
 CLOSE PQ_SUBJ;
 END;