Issue while passing an array as parameter in stored procedure

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • SekDu
    New Member
    • Feb 2023
    • 1

    Issue while passing an array as parameter in stored procedure

    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
Working...