# calling a plsql procedure through shell script

**URL:** <https://community.unix.com/t/calling-a-plsql-procedure-through-shell-script/300799>\
**Category:** Shell Programming and Scripting\
**Created:** [December 8, 2011, 9:31pm UTC](https://community.unix.com/t/calling-a-plsql-procedure-through-shell-script/300799 "2011-12-08T21:31:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![rspnf](https://community.unix.com/letter_avatar/rspnf/32/6_449c533f586939b6e5f246632a46421f.png) [@rspnf](https://community.unix.com/u/rspnf)\
**Post date:** [December 8, 2011, 9:31pm UTC](https://community.unix.com/t/calling-a-plsql-procedure-through-shell-script/300799/1 "2011-12-08T21:31:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [December 8, 2011, 9:49pm UTC](https://community.unix.com/t/calling-a-plsql-procedure-through-shell-script/300799/2 "2011-12-08T21:49:59Z")

</div>

> [@rspnf](#):
>
> 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...

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

---

<div class="post-metadata">

**Author:** ![rspnf](https://community.unix.com/letter_avatar/rspnf/32/6_449c533f586939b6e5f246632a46421f.png) [@rspnf](https://community.unix.com/u/rspnf)\
**Post date:** [December 9, 2011, 8:52pm UTC](https://community.unix.com/t/calling-a-plsql-procedure-through-shell-script/300799/3 "2011-12-09T20:52:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [December 10, 2011, 11:48am UTC](https://community.unix.com/t/calling-a-plsql-procedure-through-shell-script/300799/4 "2011-12-10T11:48:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rspnf](https://community.unix.com/letter_avatar/rspnf/32/6_449c533f586939b6e5f246632a46421f.png) [@rspnf](https://community.unix.com/u/rspnf)\
**Post date:** [December 10, 2011, 2:13pm UTC](https://community.unix.com/t/calling-a-plsql-procedure-through-shell-script/300799/5 "2011-12-10T14:13:09Z")

</div>

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.
