Shell Programming and Scripting

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.