I have a plsql procedure inside a package which is having one IN parameter .I want to call that procedure through a shell script and that IN parameter is a column of a table in the database. So my requirement is that i need to loop all the entries of that IN parameter from the table through shell script. suppose i have 100 rows in that table so i need to take 100 rows(which is IN parameter of a procedure) from the table and then process 5 at a time .so i pick 1st five rest are on wait. once those 5 are done then next five.......its a urgent requirement......any help is appreciated .......thanks in advance
Please post the "create table" statement to create the table, "insert into" statements to populate the table, and your PL/SQL procedure that you have as of now.
tyler_durden
CREATE OR REPLACE package body IRS_TIN.f1099k_data as
procedure create\_1099k_data
\(errcode out varchar2
errmsg out varchar2,
retcur out refcursor,
p\_ptfolio_id in varchar2,
p_startdt in varchar2,
p_enddt in varchar2\)
is proc_name varchar2\(50\) default 'create\_1099k\_data'||to_char\(sysdate, 'YYYYMMDDHH24MISS'\);
begin
errcode :=0;
errmsg :=null;
for ptcr in \( Select ptfolio\_id,ptfolio_name,
source\_system,network_id
from portfolio_merchants
where ptfolio_id = p\_ptfolio_id
and network_id = '06293870001' \)
loop
merge into tr\_1099k_data b
using \( Select pmx.source_system,
pmx.network_id,
pe.payee_id,
pct.pse_id, pct.epf_id,
'2011' yyyy,
sum\( case when substr \(reporting_month, 5,2\) = '01' then grs else 0 end \) jan_grs,
sum\( case when substr \(reporting_month, 5,2\) = '02' then grs else 0 end \) feb_grs,
sum\( case when substr \(reporting_month, 5,2\) = '03' then grs else 0 end \) mar_grs,
sum\( case when substr \(reporting_month, 5,2\) = '04' then grs else 0 end \) apr_grs,
sum\( case when substr \(reporting_month, 5,2\) = '05' then grs else 0 end \) may_grs,
sum\( case when substr \(reporting_month, 5,2\) = '06' then grs else 0 end \) jun_grs,
sum\( case when substr \(reporting_month, 5,2\) = '07' then grs else 0 end \) jul_grs,
sum\( case when substr \(reporting_month, 5,2\) = '08' then grs else 0 end \) aug_grs,
sum\( case when substr \(reporting_month, 5,2\) = '09' then grs else 0 end \) sep_grs,
sum\( case when substr \(reporting_month, 5,2\) = '10' then grs else 0 end \) oct_grs,
sum\( case when substr \(reporting_month, 5,2\) = '11' then grs else 0 end \) nov_grs,
sum\( case when substr \(reporting_month, 5,2\) = '12' then grs else 0 end \) dec_grs,
sum\( txn_cnt\) txn_cnt
from payee pe,
payee\_merchant_xref pmx,
portfolio_merchants pm,
ptfolio\_card\_type\_payer_xref pct,
mthly_grs mg
where pe.payee_id = pmx.payee_id
and pmx.source_system = pm.source_system
and pmx.network_id = pm.network_id
and pm.ptfolio_id = pct.ptfolio_id
and pmx.source_system = mg.source_system
and pmx.network_id = mg.network_id
and pct.product_code = mg.product_code
-- Parameters
and pmx.network_id = ptcr.network_id
and pct.ptfolio_id = ptcr.ptfolio_id
group by pe.payee_id,
pmx.source_system,
pmx.network_id,
pct.pse_id,
pct.epf_id \) t
on \( b.payee_id = t.payee_id and
b.source_system = t.source_system and
b.network_id = t.network_id and
b.pse_id = t.pse_id and
b.epf_id = t.epf_id and
b.yyyy = t.yyyy \)
when matched then
update set b.jan_grs = b.jan_grs \+ t.jan_grs,
b.feb_grs = b.feb_grs \+ t.feb_grs,
b.mar_grs = b.mar_grs \+ t.mar_grs,
b.apr_grs = b.apr_grs \+ t.apr_grs,
b.may_grs = b.may_grs \+ t.may_grs,
b.jun_grs = b.jun_grs \+ t.jun_grs,
b.jul_grs = b.jul_grs \+ t.jul_grs,
b.aug_grs = b.aug_grs \+ t.aug_grs,
b.sep_grs = b.sep_grs \+ t.sep_grs,
b.oct_grs = b.oct_grs \+ t.oct_grs,
b.nov_grs = b.nov_grs \+ t.nov_grs,
b.dec_grs = b.dec_grs \+ t.dec_grs,
b.txn_cnt = b.txn_cnt \+ t.txn_cnt,
modified_by= 'f1099k_data.pkb',
modified_date= sysdate
when not matched then
insert \( id,
payee_id,
source_system,
network_id,
pse_id,
epf_id,
jan_grs,
feb_grs,
mar_grs,
apr_grs,
may_grs,
jun_grs,
jul_grs,
aug_grs,
sep_grs,
oct_grs,
nov_grs,
dec_grs,
txn_cnt,
yyyy,
form_status,
status,
status_comments,
created_date,
created_by\)
values \(tr1099kd_seq.nextval,
t.payee_id,
t.source_system,
t.network_id,
t.pse_id, t.epf_id,
t.jan_grs,
t.feb_grs,
t.mar_grs,
t.apr_grs,
t.may_grs,
t.jun_grs,
t.jul_grs,
t.aug_grs,
t.sep_grs,
t.oct_grs,
t.nov_grs,
t.dec_grs,
t.txn_cnt,
t.yyyy,
'OR',
'PENDING', 'INITIAL',
sysdate, 'f1099k_data.pkb'\);
end loop;
end;
procedure create_1099k_data
\(errcode out varchar2,
errmsg out varchar2,
retcur out refcursor,
ptfolio_id in varchar\)
is
startdtchar\(10\) default to\_char\(last\_day\(add_months\(sysdate,-2\)\)\+1,'YYYYMMDD'\);
enddt char\(10\) default to\_char\(last\_day\(add_months\(sysdate,-1\)\),'YYYYMMDD'\);
begin
create_1099k_data (errcode, errmsg, retcur, ptfolio_id, startdt, enddt);
dbms_output.put_line('ERRCODE-'||errcode||':'||'ERRORMSG-'||errmsg);
end;
end f1099k_data;
---------- Post updated at 09:52 PM ---------- Previous update was at 09:49 PM ----------
Table name is portfolio
and column name is portfolio_id
which is also the IN parameter of the procedure
I don't quite understand the code fully.
(1) Could you please put your code within code tags? It's difficult to read as it is.
(2) You have two overloaded procedures called "create_1099k_data". One calls the other, you set p_startdt and p_enddt in the wrapper, and pass it to the other. But you don't use it in the second procedure! What's the point of passing it then?
(3) What's the purpose of the "proc_name" local variable in the invoked procedure? It's declared and assigned, but never used.
(4) There's a MERGE statement inside a CURSOR..FOR loop in the invoked procedure. You fetch data from "portfolio_merchants" in the cursor query, but then you also use the same table/view in the SELECT part of the MERGE statement. If that's the case, then you could probably do away with the CURSOR..FOR loop by adding the filter conditions of the cursor query into the SELECT part of the MERGE.
(5) Finally, your first post mentions something about "piecewise" processing, but I don't see how or where it is applicable in the posted code.
tyler_durden
Hi Durden_Tyler
I understand, but i didn't wrote this procedures ,my senior wrote this and he told me to write a script for this.
Yes you are right ,He declared proc_name but never used this in the whole procedure.
and that piecewise is the p_ptfolio_id which is the IN parameter and we are picking this IN parameter from a column of a table named P_ptfolio_id.
He said to me that In script first of all pick the whole rows of that p_ptfolio_id column and then process in the steps of 5 means pick 5 and put those in the background and when those are done pick next 5 and so on.
I appreciate your kind help.
Rspnf
---------- Post updated at 03:13 PM ---------- Previous update was at 03:11 PM ----------
We are calling the 2nd procedure in the package which itself calls the first procedure
and we are just providing one IN parameter that is p_ptfolio_id and other two he makes default p_startdt and p_enddt.