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)
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment