Hi, I have written a procedure with multiple input parameters but while calling the procedure, i am facing the error.
Procedure:
CREATE OR REPLACE PROCEDURE public.sp_creat e_svc_accounts(
IN orgid integer,
IN bggroupid integer,
IN vclabel character varying,
IN traffictype character varying,
IN channelcode character varying,
IN protocol character varying,
IN jsvcconfigout json,
IN jsvcconfigconv json,
IN transtype character varying,
IN AccType character varying,
IN SenderID character varying[],
IN status smallint
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
new_svcaccid integer;
array_in character varying[]:= array[SenderID];
var character varying;
BEGIN
If TransType = 'MT'
then
INSERT INTO public."ENT_SVC _ACCOUNTS"("iOR GID","iBGroupID ","vcLabel","vc TrafficType","v cChannelCode"," vcProtocol","jS VCConfigOut","v cTransType","vc AccType")
values (ORGID,BGGroupI D,vcLabel,Traff icType,ChannelC ode,Protocol,JS VCConfigOut,Tra nsType,AccType) returning "iSVCAccoun tID" into new_svcaccid;
else
INSERT INTO public."ENT_SVC _ACCOUNTS"("iOR GID","iBGroupID ","vcLabel","vc TrafficType","v cChannelCode"," vcProtocol","jS VCConfigConv"," vcTransType")
values (ORGID,BGGroupI D,vcLabel,Traff icType,ChannelC ode,Protocol,JS VCConfigConv,Tr ansType,AccType ) returning "iSVCAccoun tID" into new_svcaccid;
END IF;
update public."ENT_SVC _ACCOUNTS" set "vcSVCID"=conca t(new_svcaccid, trunc( extract(epoch from CURRENT_TIMESTA MP)*1000)) where "iSVCAccountID" =new_svcaccid;
foreach var slice 1 in ARRAY array_in
loop
INSERT INTO public."ENT_SVC ACC_SENDERID_MA P"("iORGID","iA ccountID","vcSe nderID","iStatu s")
values(orgid,ne w_svcaccid,var, status);
end loop;
commit;
end
$BODY$;
Call:
call sp_create_svc_a ccounts(99999,9 ,'WA9_DEMO9_SVC 9','PROMO','WAP P','HTTP','{"iT PS": 20,"FBRoute": 3,"vcIPList": "0.0.0.0","DRSu bType": "","iMaxRet ry": 2,"FBClientID ": "","bIsEncrypt" : true,"DRNotifyU RL": "http://localhost:8080/webhook","EVNot ifyURL": "http://localhost:8080/webhook","FBAcc ountID": 0,"MONotifyURL" : "http://localhost:8080/webhook","bIsFa llback": true,"iMaxCBRet ry": 1,"DRNotifyType ": "cb","bIsProfCh eck": false,"FBChanne lCode": "","bIsSaasEnab led": true,"iMaxConne ctions": 10}',null,'MT', 'POSTPAID','{te st,defaultGroup }',1);
Error:
ERROR: procedure sp_create_svc_a ccounts(integer , integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: call sp_create_svc_a ccounts(9999,9, 'WA9_DEMO9_SVC9 ','PROMO',...
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6
Procedure:
CREATE OR REPLACE PROCEDURE public.sp_creat e_svc_accounts(
IN orgid integer,
IN bggroupid integer,
IN vclabel character varying,
IN traffictype character varying,
IN channelcode character varying,
IN protocol character varying,
IN jsvcconfigout json,
IN jsvcconfigconv json,
IN transtype character varying,
IN AccType character varying,
IN SenderID character varying[],
IN status smallint
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
new_svcaccid integer;
array_in character varying[]:= array[SenderID];
var character varying;
BEGIN
If TransType = 'MT'
then
INSERT INTO public."ENT_SVC _ACCOUNTS"("iOR GID","iBGroupID ","vcLabel","vc TrafficType","v cChannelCode"," vcProtocol","jS VCConfigOut","v cTransType","vc AccType")
values (ORGID,BGGroupI D,vcLabel,Traff icType,ChannelC ode,Protocol,JS VCConfigOut,Tra nsType,AccType) returning "iSVCAccoun tID" into new_svcaccid;
else
INSERT INTO public."ENT_SVC _ACCOUNTS"("iOR GID","iBGroupID ","vcLabel","vc TrafficType","v cChannelCode"," vcProtocol","jS VCConfigConv"," vcTransType")
values (ORGID,BGGroupI D,vcLabel,Traff icType,ChannelC ode,Protocol,JS VCConfigConv,Tr ansType,AccType ) returning "iSVCAccoun tID" into new_svcaccid;
END IF;
update public."ENT_SVC _ACCOUNTS" set "vcSVCID"=conca t(new_svcaccid, trunc( extract(epoch from CURRENT_TIMESTA MP)*1000)) where "iSVCAccountID" =new_svcaccid;
foreach var slice 1 in ARRAY array_in
loop
INSERT INTO public."ENT_SVC ACC_SENDERID_MA P"("iORGID","iA ccountID","vcSe nderID","iStatu s")
values(orgid,ne w_svcaccid,var, status);
end loop;
commit;
end
$BODY$;
Call:
call sp_create_svc_a ccounts(99999,9 ,'WA9_DEMO9_SVC 9','PROMO','WAP P','HTTP','{"iT PS": 20,"FBRoute": 3,"vcIPList": "0.0.0.0","DRSu bType": "","iMaxRet ry": 2,"FBClientID ": "","bIsEncrypt" : true,"DRNotifyU RL": "http://localhost:8080/webhook","EVNot ifyURL": "http://localhost:8080/webhook","FBAcc ountID": 0,"MONotifyURL" : "http://localhost:8080/webhook","bIsFa llback": true,"iMaxCBRet ry": 1,"DRNotifyType ": "cb","bIsProfCh eck": false,"FBChanne lCode": "","bIsSaasEnab led": true,"iMaxConne ctions": 10}',null,'MT', 'POSTPAID','{te st,defaultGroup }',1);
Error:
ERROR: procedure sp_create_svc_a ccounts(integer , integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: call sp_create_svc_a ccounts(9999,9, 'WA9_DEMO9_SVC9 ','PROMO',...
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6