Tuesday, June 21, 2011

PLS-00307: too many declarations of ''"" match this call

problem:- ( PLS-00307: too many declarations of 'CHECK_VAL' match this call )

Today when i am execute following block its says the above error.
after i am searching the problem, i found the belows solution .

begin
begin
SELECT UPPER(USER) INTO V_USER FROM DUAL;
exception
when others then
null;
end;
IF V_MODULE LIKE UPPER ('%SQL%') OR V_MODULE LIKE UPPER ('%TOAD%') OR V_USER <>'STLBAS' THEN
if inserting then
stllog.audit_pkg1.check_val('stlbas','stbrnlm1', 'BRANCD', :new.BRANCD, :old.BRANCD,:new.OPRSTAMP, :new.SRLNUM );
stllog.audit_pkg1.check_val( 'stlbas','stbrnlm1', 'ACTYPE', :new.ACTYPE, :old.ACTYPE,:new.OPRSTAMP,:new.SRLNUM );
end if;
end if ;
end ;


Here check_val is a procedure under audit_pkg1 package,
check_val procedure's last parameter is varchar2 and here :new.SRLNUM is number .

so Database is'nt able to convert the this last parameter variable from VARCHAR2 to NUMBER
to match the call parameters used in the check_val procedure .



solution:-
==================

plsql always try to convert data types of the parameters but sometimes fail,
so this problem PLS-00307: too many declarations of 'CHECK_VAL' match this call , occure.


solution 1 :- try to convert :new.SRLNUM value to to_char(:new.SRLNUM) ;


begin
begin
SELECT UPPER(USER) INTO V_USER FROM DUAL;
exception
when others then
null;
end;
IF V_MODULE LIKE UPPER ('%SQL%') OR V_MODULE LIKE UPPER ('%TOAD%') OR V_USER <>'STLBAS' THEN
if inserting then
stllog.audit_pkg1.check_val('stlbas','stbrnlm1', 'BRANCD', :new.BRANCD, :old.BRANCD,:new.OPRSTAMP, to_char(:new.SRLNUM));
stllog.audit_pkg1.check_val( 'stlbas','stbrnlm1', 'ACTYPE', :new.ACTYPE, :old.ACTYPE,:new.OPRSTAMP,to_char(:new.SRLNUM));
end if;
end if ;
end ;

or

solution - 2 :- write a overloading procedure for check_val with last parameter is number value.


see more on metalink node (ID 1232114.1)