473,387 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Issue while passing an array as parameter in stored procedure

1
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_create_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"("iORGID","iBGroupID","vc Label","vcTrafficType","vcChannelCode","vcProtocol ","jSVCConfigOut","vcTransType","vcAccType")
values (ORGID,BGGroupID,vcLabel,TrafficType,ChannelCode,P rotocol,JSVCConfigOut,TransType,AccType) returning "iSVCAccountID" into new_svcaccid;
else
INSERT INTO public."ENT_SVC_ACCOUNTS"("iORGID","iBGroupID","vc Label","vcTrafficType","vcChannelCode","vcProtocol ","jSVCConfigConv","vcTransType")
values (ORGID,BGGroupID,vcLabel,TrafficType,ChannelCode,P rotocol,JSVCConfigConv,TransType,AccType) returning "iSVCAccountID" into new_svcaccid;
END IF;

update public."ENT_SVC_ACCOUNTS" set "vcSVCID"=concat(new_svcaccid,trunc( extract(epoch from CURRENT_TIMESTAMP)*1000)) where "iSVCAccountID"=new_svcaccid;

foreach var slice 1 in ARRAY array_in
loop
INSERT INTO public."ENT_SVCACC_SENDERID_MAP"("iORGID","iAccoun tID","vcSenderID","iStatus")
values(orgid,new_svcaccid,var,status);
end loop;

commit;
end
$BODY$;

Call:
call sp_create_svc_accounts(99999,9,'WA9_DEMO9_SVC9','P ROMO','WAPP','HTTP','{"iTPS": 20,"FBRoute": 3,"vcIPList": "0.0.0.0","DRSubType": "","iMaxRetry": 2,"FBClientID": "","bIsEncrypt": true,"DRNotifyURL": "http://localhost:8080/webhook","EVNotifyURL": "http://localhost:8080/webhook","FBAccountID": 0,"MONotifyURL": "http://localhost:8080/webhook","bIsFallback": true,"iMaxCBRetry": 1,"DRNotifyType": "cb","bIsProfCheck": false,"FBChannelCode": "","bIsSaasEnabled": true,"iMaxConnections": 10}',null,'MT','POSTPAID','{test,defaultGroup}',1) ;

Error:
ERROR: procedure sp_create_svc_accounts(integer, integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: call sp_create_svc_accounts(9999,9,'WA9_DEMO9_SVC9','PR OMO',...
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6
Feb 15 '23 #1
0 4119

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Bruce Lester | last post by:
I am trying to get the DTS Execute SQL task to run a simple sql server 2000 no-parameter stored procedure. The procedure runs without error when using SQL Analyzer but DTS Execute SQL reports...
0
by: VictorCorey | last post by:
Is it possible to use a multi-parameter query in .NET Here's the method Public Function SearchCatalog(ByVal searchString As String, ByVal allWords As String) As OleDbDataReade ' Create the...
2
by: Bob | last post by:
I'm new to Access projects and SQL server and am not a veteran VB programmer. There's a cry for help! I'm attempting to print the current form on screen by using a command button which the user...
7
by: M | last post by:
Is there any way to modify the following code so I can run it with any number of paramNames as well as any number of paramValues? So far it works with an array of paramValues, but since you can...
1
by: Michal Hlavac | last post by:
DECLARE groups integer; tmp RECORD; tmpi integer; BEGIN FOR tmp IN SELECT i_group_id FROM l_group_to_user WHERE i_user_id = $1 LOOP SELECT i_group_id INTO tmpi FROM s_group WHERE i_group_id =...
1
by: vncntj | last post by:
I have a C#.NET that simply passes 6 values to a Stored Procedure. But I'm trying to get the (Default.aspx.cs page) to handle passing the values to the sp. The goal is to pass the values and see...
2
by: adukuri | last post by:
Hi, I am new to jsp coding and I need some help in coding. 1. Writing to a text file from a result set. need to open a new txt file on a unix box and store it. 2. Paging. To generate...
1
by: anniefs | last post by:
hi guys plz help me in this code i m trying to call the vb function in javascript function and im also passing parameter like array function save() { var name; p2_array=1; p2_array=2;...
0
Saii
by: Saii | last post by:
How can we pass a perl array to stored procedure in Oracle. The parameter I am using in Oracle is type of table of varchar(500). I am using the system command in perl to execute the procedure ...
2
by: hemantc87 | last post by:
i have created this function with a parameter offset and i want to access the database using stored procedure but the code and stored procedure i have written below is not working...what is the right...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.