Home » SQL & PL/SQL » SQL & PL/SQL » Need Help on Pl/Sql (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Need Help on Pl/Sql [message #670639] Tue, 17 July 2018 10:28 Go to previous message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi All,

My Requirement is to transpose data in a table and then do a join with another table and finally insert the fetched data in to a Table.I'm new to PL/SQL.

I tried writing a little not sure if it is correct.Can Someone help correcting the error in this code .I would like to register this as procedure finally.

Any help would be appreciated and it would help me learn.

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;
 
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: query performance
Next Topic: Get last record from another table
Goto Forum:
  


Current Time: Wed Dec 11 15:21:43 CST 2019