Home » SQL & PL/SQL » SQL & PL/SQL » output of a refcursor which is inside a loop (Oracle 11g)
output of a refcursor which is inside a loop [message #616408] |
Mon, 16 June 2014 11:40 |
|
ashish_2005
Messages: 4 Registered: June 2014 Location: Bangalore
|
Junior Member |
|
|
I have a written a proc. The input parameter of the proc is a string of batch ids which comes like '1234,6788,8888,9999' and now
I want that output of this proc for each execution of passed batch ids should get the records for all the passed batches. As of now I am only getting the records for the last passed batch id.
for eg in this case I am getting the records only for the last processed batch 354538. I want the records for both batch ids '280757,354538'
Code is right here
set serveroutput on
Variable V Refcursor;
Exec Rptco.Dub_Bat_Comm_Select('280757,354538' , :v);
print :v;
create or replace
PROCEDURE DUB_BAT_COMM_SELECT(
p_batch_id IN VARCHAR,
result_out OUT sys_refcursor)
IS
p_batch_type varchar2(10);
CURSOR cur IS
WITH qry AS (
SELECT p_batch_id batch_string
FROM dual)
SELECT regexp_substr (batch_string, '[^,]+', 1, ROWNUM) batch
FROM qry
CONNECT BY LEVEL <= LENGTH(regexp_replace (batch_string, '[^,]+')) + 1 ;
BEGIN
FOR rec IN cur LOOP
SELECT batch_type into p_batch_type from MSA.CL_PR_TAPE_BATCH where tape_batch_id = to_number( rec.batch) ; ---p_batch_id;
dbms_output.put_line('batch_id='||rec.batch);
dbms_output.put_line('batch_type='||p_batch_type);
IF p_batch_type ='CD' then
OPEN result_out FOR
SELECT ge_net.call_sign,
logo_info.network_logo_filename,
logo_info.corp_logo_filename,
reqdubbatch.tape_batch_id batch_id,
reqdubbatch.title,
NULL combo_ad_id,
reqdubbatch.revision_num,
TO_CHAR(reqdubbatch.chg_dt,'MM/DD/YYYY'),
TO_CHAR(reqdubbatch.chg_dt,'HH:MI PM'),
ge_user.fname,
ge_user.lname,
reqnettape.house,
orgisci.material_identifier,
DECODE(reqnettape.high_defn_flag, 'Y','HD','SD'),
actnetcut.combo_isci_flag COMBO_ISCI_FLAG,
adver.name advertiser,
orgisci.isci_code ad_id,
orgisci.isci_type ad_id_type,
brand.name brand,
copylen.value,
reqnetcut.actual_req_code,
actnetcut.compress_flag,
copylen.name,
actcutisci.component_length
FROM msa.cl_org_isci orgisci
INNER JOIN msa.ge_advertisers adver ON orgisci.adver_id = adver.adver_id
INNER JOIN msa.ge_copylens copylen ON orgisci.copylen_id = copylen.copylen_id
INNER JOIN msa.cl_agency_cut acut ON orgisci.isci_code_id = acut.isci_code_id
INNER JOIN msa.cl_agency_tape atape ON acut.agency_tape_id = atape.agency_tape_id
INNER JOIN msa.cl_net_isci netisci ON orgisci.isci_code_id = netisci.isci_code_id
INNER JOIN msa.ge_net ge_net ON netisci.net_id = ge_net.net_id
INNER JOIN msa.cl_pr_tape_batch_net cl_pr_tape_batch_net ON cl_pr_tape_batch_net.net_id = ge_net.net_id
AND cl_pr_tape_batch_net.net_id = acut.net_id
INNER JOIN msa.cl_net_tape_cut_isci actcutisci ON netisci.net_isci_id = actcutisci.net_isci_id
INNER JOIN msa.cl_net_tape_cut actnetcut ON actcutisci.net_tape_cut_id = actnetcut.net_tape_cut_id
INNER JOIN msa.cl_pr_net_tape actnettape ON actnetcut.net_tape_id = actnettape.net_tape_id
LEFT OUTER JOIN msa.cl_net_tape_cut_isci reqcutisci ON netisci.net_isci_id = reqcutisci.net_isci_id
INNER JOIN msa.cl_net_tape_cut reqnetcut ON reqcutisci.net_tape_cut_id = reqnetcut.net_tape_cut_id
INNER JOIN msa.cl_pr_tape_batch reqdubbatch ON reqnetcut.dub_tape_batch_id = reqdubbatch.tape_batch_id
INNER JOIN msa.ge_user ge_user ON reqdubbatch.chg_user = ge_user.gab_id
LEFT OUTER JOIN msa.cl_pr_net_tape reqnettape ON reqnetcut.net_tape_id = reqnettape.net_tape_id
LEFT OUTER JOIN msa.ge_brand brand ON orgisci.brand_id = brand.brand_id
LEFT OUTER JOIN
(SELECT nl.file_name network_logo_filename,
bpl.file_name corp_logo_filename,
g_n.legacy_net_id legacy_net_id
FROM buspty.network n
INNER JOIN gtwy.gl2gt_network g_n
ON n.uuid = g_n.network_uuid
LEFT OUTER JOIN buspty.network_logo nl
ON n.uuid = nl.network_uuid
LEFT OUTER JOIN buspty.business_party_logo bpl
ON n.business_org_uuid =bpl.business_party_uuid
) logo_info ON logo_info.legacy_net_id = ge_net.net_id
WHERE
reqnetcut.Actual_Req_Code ='R'
AND Actnetcut.Actual_Req_Code = 'A'
AND Reqnetcut.Dub_Tape_Batch_Id = to_number(rec.batch)
UNION
SELECT ge_net.call_sign,
logo_info.network_logo_filename,
logo_info.corp_logo_filename,
actdubbatch.tape_batch_id batch_id,
actdubbatch.title,
ad_id_tab.isci_code combo_ad_id,
actdubbatch.revision_num,
TO_CHAR(ActDubBatch.Chg_Dt,'MM/DD/YYYY'),
TO_CHAR(ActDubBatch.Chg_Dt,'HH:MI PM'),
ge_user.fname,
ge_user.lname,
actnettape.house,
orgisci.material_identifier,
DECODE(actnettape.high_defn_flag, 'Y','HD','SD'),
actnetcut.combo_isci_flag COMBO_ISCI_FLAG,
adver.name advertiser,
orgisci.isci_code ad_id,
orgisci.isci_type ad_id_type,
brand.name brand,
copylen.value,
actnetcut.actual_req_code,
actnetcut.compress_flag,
copylen.name,
actcutisci.component_length
FROM MSA.cl_org_isci OrgISCI
INNER JOIN msa.ge_advertisers adver ON orgisci.adver_id = adver.adver_id
INNER JOIN msa.ge_copylens copylen ON orgisci.copylen_id = copylen.copylen_id
INNER JOIN msa.cl_agency_cut acut ON acut.isci_code_id = orgisci.isci_code_id
INNER JOIN msa.cl_agency_tape atape ON acut.agency_tape_id = atape.agency_tape_id
INNER JOIN msa.cl_net_isci netisci ON orgisci.isci_code_id = netisci.isci_code_id
INNER JOIN msa.cl_net_tape_cut_isci actcutisci ON netisci.net_isci_id = actcutisci.net_isci_id
INNER JOIN msa.cl_net_tape_cut actnetcut ON actcutisci.net_tape_cut_id = actnetcut.net_tape_cut_id
INNER JOIN msa.cl_pr_net_tape actnettape ON actnetcut.net_tape_id = actnettape.net_tape_id
INNER JOIN msa.cl_pr_tape_batch actdubbatch ON actnetcut.dub_tape_batch_id = actdubbatch.tape_batch_id
INNER JOIN msa.ge_net ge_net ON netisci.net_id = ge_net.net_id
INNER JOIN msa.cl_pr_tape_batch_net cl_pr_tape_batch_net ON cl_pr_tape_batch_net.net_id = ge_net.net_id
AND cl_pr_tape_batch_net.net_id = acut.net_id
INNER JOIN msa.ge_user ge_user ON actdubbatch.chg_user = ge_user.gab_id
LEFT OUTER JOIN msa.ge_brand brand ON orgisci.brand_id = brand.brand_id
LEFT OUTER JOIN
(SELECT nl.file_name network_logo_filename,
bpl.file_name corp_logo_filename,
g_n.legacy_net_id legacy_net_id
FROM buspty.network n
INNER JOIN gtwy.gl2gt_network g_n
ON n.uuid = g_n.network_uuid
LEFT OUTER JOIN buspty.network_logo nl
ON n.uuid = nl.network_uuid
LEFT OUTER JOIN buspty.business_party_logo bpl
ON n.business_org_uuid =bpl.business_party_uuid
) logo_info ON logo_info.legacy_net_id = ge_net.net_id
Inner Join (
Select Isci_Code , isci_code_id From Msa.Cl_Org_Isci Where Isci_Code_Id In (
Select Isci_Code_Id From Msa.Cl_Net_Isci Where Net_Isci_Id In (Select Net_Isci_Id From Msa.Cl_Net_Tape_Cut_Isci Where Net_Tape_Cut_Id In (
Select Net_Tape_Cut_Id From Msa.Cl_Net_Tape_Cut Where Combo_Isci_Flag='Y' And Dub_Tape_Batch_Id= to_number(rec.batch)
)))) ad_id_tab on orgisci.isci_code_id = netisci.isci_code_id
Where
actnetcut.dub_tape_batch_id = to_number(rec.batch)
UNION
SELECT ge_net.call_sign,
logo_info.network_logo_filename,
logo_info.corp_logo_filename,
actdubbatch.tape_batch_id batch_id,
actdubbatch.title,
NULL combo_ad_id,
actdubbatch.revision_num,
TO_CHAR(ActDubBatch.Chg_Dt,'MM/DD/YYYY'),
TO_CHAR(ActDubBatch.Chg_Dt,'HH:MI PM'),
ge_user.fname,
ge_user.lname,
actnettape.house,
orgisci.material_identifier,
DECODE(actnettape.high_defn_flag, 'Y','HD','SD'),
actnetcut.combo_isci_flag COMBO_ISCI_FLAG,
adver.name advertiser,
orgisci.isci_code ad_id,
orgisci.isci_type ad_id_type,
brand.name brand,
copylen.value,
actnetcut.actual_req_code,
actnetcut.compress_flag,
copylen.name,
actcutisci.component_length
FROM MSA.cl_org_isci OrgISCI
INNER JOIN msa.ge_advertisers adver ON orgisci.adver_id = adver.adver_id
INNER JOIN msa.ge_copylens copylen ON orgisci.copylen_id = copylen.copylen_id
INNER JOIN msa.cl_agency_cut acut ON acut.isci_code_id = orgisci.isci_code_id
INNER JOIN msa.cl_agency_tape atape ON acut.agency_tape_id = atape.agency_tape_id
INNER JOIN msa.cl_net_isci netisci ON orgisci.isci_code_id = netisci.isci_code_id
INNER JOIN msa.cl_net_tape_cut_isci actcutisci ON netisci.net_isci_id = actcutisci.net_isci_id
INNER JOIN msa.cl_net_tape_cut actnetcut ON actcutisci.net_tape_cut_id = actnetcut.net_tape_cut_id
INNER JOIN msa.cl_pr_net_tape actnettape ON actnetcut.net_tape_id = actnettape.net_tape_id
INNER JOIN msa.cl_pr_tape_batch actdubbatch ON actnetcut.dub_tape_batch_id = actdubbatch.tape_batch_id
INNER JOIN msa.ge_net ge_net ON netisci.net_id = ge_net.net_id
INNER JOIN msa.cl_pr_tape_batch_net cl_pr_tape_batch_net ON cl_pr_tape_batch_net.net_id = ge_net.net_id
AND cl_pr_tape_batch_net.net_id = acut.net_id
INNER JOIN msa.ge_user ge_user ON actdubbatch.chg_user = ge_user.gab_id
LEFT OUTER JOIN msa.ge_brand brand ON orgisci.brand_id = brand.brand_id
LEFT OUTER JOIN
(SELECT nl.file_name network_logo_filename,
bpl.file_name corp_logo_filename,
g_n.legacy_net_id legacy_net_id
FROM buspty.network n
INNER JOIN gtwy.gl2gt_network g_n
ON n.uuid = g_n.network_uuid
LEFT OUTER JOIN buspty.network_logo nl
ON n.uuid = nl.network_uuid
LEFT OUTER JOIN buspty.business_party_logo bpl
ON n.business_org_uuid =bpl.business_party_uuid
) logo_info ON logo_info.legacy_net_id = ge_net.net_id
WHERE
actnetcut.actual_req_code = 'A'
AND NOT EXISTS
(SELECT NULL
FROM msa.cl_net_tape_cut_isci reqcutisci,
msa.cl_net_tape_cut reqnetcut
WHERE netisci.net_isci_id = reqcutisci.net_isci_id
AND reqcutisci.net_tape_cut_id = reqnetcut.net_tape_cut_id
AND Reqnetcut.actual_req_code = 'R')
AND actnetcut.dub_tape_batch_id = to_number(rec.batch)
UNION
SELECT ge_net.call_sign,
logo_info.network_logo_filename,
logo_info.corp_logo_filename,
actdubbatch.tape_batch_id batch_id,
actdubbatch.title,
NULL combo_ad_id,
actdubbatch.revision_num,
TO_CHAR(actdubbatch.chg_dt,'MM/DD/YYYY'),
TO_CHAR(actdubbatch.chg_dt,'HH:MI PM'),
ge_user.fname,
ge_user.lname,
actnettape.house,
orgisci.material_identifier,
DECODE(actnettape.high_defn_flag, 'Y','HD','SD'),
actnetcut.combo_isci_flag COMBO_ISCI_FLAG,
adver.name advertiser,
orgisci.isci_code ad_id,
orgisci.isci_type ad_id_type,
brand.name brand,
copylen.value,
actnetcut.actual_req_code,
actnetcut.compress_flag,
copylen.name,
actcutisci.component_length
FROM msa.cl_org_isci orgisci
INNER JOIN msa.ge_advertisers adver ON orgisci.adver_id = adver.adver_id
INNER JOIN msa.ge_copylens copylen ON orgisci.copylen_id = copylen.copylen_id
INNER JOIN msa.cl_agency_cut acut ON orgisci.isci_code_id = acut.isci_code_id
INNER JOIN msa.cl_agency_tape atape ON acut.agency_tape_id = atape.agency_tape_id
INNER JOIN msa.cl_net_isci netisci ON orgisci.isci_code_id = netisci.isci_code_id
AND ACut.ISCI_CODE_ID = OrgISCI.ISCI_CODE_ID
INNER JOIN msa.cl_net_tape_cut_isci actcutisci ON netisci.net_isci_id = actcutisci.net_isci_id
INNER JOIN msa.cl_net_tape_cut actnetcut ON actcutisci.net_tape_cut_id = actnetcut.net_tape_cut_id
INNER JOIN msa.cl_pr_net_tape actnettape ON actnetcut.net_tape_id = actnettape.net_tape_id
INNER JOIN msa.cl_pr_tape_batch actdubbatch ON actnetcut.dub_tape_batch_id = actdubbatch.tape_batch_id
INNER JOIN msa.ge_net ge_net ON netisci.net_id = ge_net.net_id
INNER JOIN msa.cl_pr_tape_batch_net cl_pr_tape_batch_net ON cl_pr_tape_batch_net.net_id = ge_net.net_id
AND cl_pr_tape_batch_net.net_id = acut.net_id
INNER JOIN msa.ge_user ge_user ON actdubbatch.chg_user = ge_user.gab_id
LEFT OUTER JOIN msa.ge_brand brand ON orgisci.brand_id = brand.brand_id
LEFT OUTER JOIN
(SELECT nl.file_name network_logo_filename,
bpl.file_name corp_logo_filename,
g_n.legacy_net_id legacy_net_id
FROM buspty.network n
INNER JOIN gtwy.gl2gt_network g_n
ON n.uuid = g_n.network_uuid
LEFT OUTER JOIN buspty.network_logo nl
ON n.uuid = nl.network_uuid
LEFT OUTER JOIN buspty.business_party_logo bpl
ON n.business_org_uuid =bpl.business_party_uuid
) logo_info ON logo_info.legacy_net_id = ge_net.net_id
WHERE
actnetcut.actual_req_code = 'A'
AND EXISTS
(SELECT NULL
FROM msa.cl_net_tape_cut_isci reqcutisci,
msa.cl_net_tape_cut reqnetcut
WHERE netisci.net_isci_id = reqcutisci.net_isci_id
AND reqcutisci.net_tape_cut_id = reqnetcut.net_tape_cut_id
AND reqnetcut.dub_tape_batch_id IS NULL
AND reqnetcut.actual_req_code = 'R')
AND actnetcut.dub_tape_batch_id = to_number(rec.batch)
ORDER BY 9 ;
ELSE
OPEN result_out FOR
SELECT ge_net.call_sign,
cl_pr_tape_batch.tape_batch_id,
cl_pr_tape_batch.title,
cl_pr_tape_batch.revision_num,
TO_CHAR(cl_pr_tape_batch.chg_dt,'MM/DD/YYYY'),
TO_CHAR(cl_pr_tape_batch.chg_dt,'HH:MI PM'),
ge_user.fname,
ge_user.lname,
cl_pr_net_tape.house,
cl_pr_net_tape.material_identifier,
DECODE(pr_net_tape_cut_promo.high_defn_flag, 'Y','HD','SD'),
pr_promo.title,
pr_promo.reference_num,
ge_copylens.value,
pr_promo.promo_comment
FROM MSA.cl_pr_net_tape cl_pr_net_tape
INNER JOIN MSA.pr_net_tape_cut_promo pr_net_tape_cut_promo
ON cl_pr_net_tape.net_tape_id = pr_net_tape_cut_promo.net_tape_id
INNER JOIN MSA.cl_pr_tape_batch cl_pr_tape_batch
ON cl_pr_tape_batch.tape_batch_id = pr_net_tape_cut_promo.dub_tape_batch_id
INNER JOIN MSA.cl_pr_tape_batch_net cl_pr_tape_batch_net
ON cl_pr_tape_batch.tape_batch_id = cl_pr_tape_batch_net.tape_batch_id
INNER JOIN MSA.ge_user ge_user
ON cl_pr_tape_batch.chg_user = ge_user.gab_id
INNER JOIN MSA.ge_net ge_net
ON ge_net.net_id = cl_pr_tape_batch_net.net_id
INNER JOIN MSA.pr_promo
ON pr_promo.promo_id = pr_net_tape_cut_promo.promo_id
INNER JOIN MSA.ge_copylens ge_copylens
ON ge_copylens.copylen_id = pr_promo.copylen_id
WHERE cl_pr_tape_batch.tape_batch_id = to_number(rec.batch)
ORDER BY cl_pr_tape_batch.tape_batch_id,cl_pr_net_tape.house;
END IF;
END LOOP;
END;
/
execution
set serveroutput on
Variable V Refcursor;
Exec Rptco.Dub_Bat_Comm_Select('280757,354538' , :v);
print :v;
[EDITED by LF: applied [spoiler] & some [code] tags]
[Updated on: Tue, 17 June 2014 02:53] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: output of a refcursor which is inside a loop [message #616464 is a reply to message #616460] |
Tue, 17 June 2014 03:45 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
This is what i understand about OP's requirement :
1. Some batch ids are input parameter as a string.
2. Based on each batch id data needs to be fetched.
3. OP used a loop over these batch ids hoping to combine the result set of each loop in the ref corsor --> This is where he went wrong. Since the result set that the ref cursor would hold is only that of the last loop.
I agree with CM, just open cursor for all the queries for each batch id using UNION, keep the cursor open.
|
|
|
|
Re: output of a refcursor which is inside a loop [message #616468 is a reply to message #616467] |
Tue, 17 June 2014 04:08 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Hmm, I missed that point. Well, if OP manages to fetch data based on different batch ids using a single query(which doesn't seem in his case as he has an IF-ELSE condition based on batch ids) then instead of converting the batch ids into rows and doing it in loop, an IN condition for batch ids would have sufficed.
I am thinking of having the result set in a GTT and then open cursor for select * from GTT.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 07:23:04 CDT 2024
|