Hi, I'm executing the below proced

[rk68]

Hi,
I'm executing the below procedure from SQL command prompt.
>execute pr_points_cal(9876543210,250,to_date('27-05-19 2:39:00 PM','dd-mm-yyyy HH:MI:SS AM'),789,987);

Getting the following error;
ORA-06550: line 2, column 7:
PLS-00306: wrong number or types of arguments in call to 'PR_POINTS_CAL'
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored

Please find below my procedure. There are 5 input parameters & 3 output parameters.
Is it because of the output parameter? When I execute the same procedure in PL/SQL it works fine.

=======================================================
CREATE OR REPLACE PROCEDURE "PR_POINTS_CAL" (
p_mobile in number, -- Mobile #
p_amt in number, -- Invoice Amt
p_dt in date, -- Invoice Dt
p_invno in number, -- Invoice No.
p_propid in number, -- Property Id
o_errorcd out number, -- Error Code
o_trnpts out number, -- Points earned for trn
o_totpts out number -- Total points
)
is
v_tier varchar2(30) := '' ; -- Tier
v_ptval number := 0 ;
v_ptper number := 0 ;
v_points number := 0 ;
v_mobile number := 0 ;
v_trn number := 0 ;
BEGIN

---- Check for Mobile no. in Member Master ----
select count(*) into v_mobile
from t_mbr_mst t
where t.mbr_mobile1 = p_mobile
and t.mbr_flg_active = 'Y' ;

if v_mobile > 0 then

---- Check for existing TRN data in Transaction table ----
select count(*) into v_trn
from t_transaction q
where q.trn_inv_no = p_invno
and q.trn_cust_mobile = p_mobile
and q.trn_inv_amt = p_amt
and q.trn_inv_dt = p_dt
and q.trn_prop_id = p_propid ;

if v_trn = 0 then

---- Insert in Transaction table ----

insert into t_transaction p (p.trn_inv_no,
p.trn_cust_mobile,
p.trn_prop_id,
p.trn_inv_dt,
p.trn_inv_amt)
values (p_invno,
p_mobile,
p_propid,
p_dt,
p_amt);

commit;

---- Calculate Points ----
select t.mbr_tier into v_tier
from t_mbr_mst t
where t.mbr_mobile1 = p_mobile
and t.mbr_flg_active = 'Y' ;

select x.tier_point, x.tier_per_val into v_ptval, v_ptper
from t_tier_master x
where upper(trim(x.tier_desc)) = upper(trim(v_tier))
and x.tier_actv_flg = 'Y' ;

v_points := round((p_amt * v_ptval)/v_ptper,0);

o_trnpts := v_points;

insert into t_points_detail y (y.summ_mobile_no,
y.summ_inv_no,
y.summ_inv_dt,
y.summ_point_cd,
y.summ_amt,
y.summ_point_val,
y.summ_per_pt_val,
y.summ_points,
y.summ_pt_process)
values (p_mobile,
p_invno,
p_dt,
10,
p_amt,
v_ptval,
v_ptper,
v_points,
'Y');

commit;

update t_mbr_mst r
set r.mbr_points = r.mbr_points + v_points
where r.mbr_mobile1 = p_mobile
and r.mbr_flg_active = 'Y' ;

commit;

select u.mbr_points into o_totpts
from t_mbr_mst u
where u.mbr_mobile1 = p_mobile
and u.mbr_flg_active = 'Y' ;

update t_transaction l
set l.trn_points = v_points,
l.trn_point_cal = 'Y',
l.trn_proc_dt = sysdate
where l.trn_inv_no = p_invno
and l.trn_cust_mobile = p_mobile
and l.trn_inv_amt = p_amt
and l.trn_inv_dt = p_dt
and l.trn_prop_id = p_propid ;

commit;

o_errorcd := 0;

else
o_errorcd := 21;
end if;

else
o_errorcd := 11;
end if;

end PR_POINTS_CAL;
=================================

TIA,
Raj

 

[Beilstwh]

yes, all parameters (except if in and defaulted) must be in the call. Try the following

DECLARE
    O_errorcd   NUMBER;
    O_trnpts    NUMBER;
    O_totpts    NUMBER;
BEGIN
    Pr_points_cal (9876543210,
                   250,
                   TO_DATE ('27-05-19 2:39:00 PM', 'dd-mm-yyyy HH:MI:SS AM'),
                   789,
                   987,
                   O_errorcd,
                   O_trnpts,
                   O_totpts);
END;
/

Bill
Lead Application Developer
New York State, USA