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 |
|
vharish006
Messages: 70 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;
|
|
|
Goto Forum:
Current Time: Fri Apr 26 19:44:34 CDT 2024
|