Wrong number or types of arguments in call to 'SF_GET_CLIENTS_QUERY'

Wrong number or types of arguments in call to 'SF_GET_CLIENTS_QUERY'

Hi Team, Facing this error, when we try to call a specific Oracle function, which has a input of type Table.  there are no other mismatch on the number or type of arguments. Issue only when we use this particular parameter(atbl_client_nr). Below i have given the complete code from .Net and also the Oracle functions used in this package.

.NET Code:

private OracleCommand CreateClientSearchCommand(OracleCommand cmd, ClientSearchInputModel searchModel, List<Guid> clientIds, bool includeInactiveRecords)

        {

            cmd.CommandText = _context.SPDefaultSchema + "." + CommandText.Client.SF_GET_CLIENTS_QUERY;

            cmd.CommandType = CommandType.StoredProcedure;

 

            cmd.Parameters.Add("lrc_result", OracleDbType.Cursor).Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add("as_check_active_only", OracleDbType.VarChar, ParameterDirection.Input).Value = YesNo.Convert(!includeInactiveRecords);

            cmd.Parameters.Add("an_company_external_id", OracleDbType.Raw).Value = _companyId;

 

 

            cmd.Parameters.Add("as_name", OracleDbType.VarChar, ParameterDirection.Input).Value = searchModel?.Name;

            cmd.Parameters.Add("as_address", OracleDbType.VarChar, ParameterDirection.Input).Value = searchModel?.Address;

            cmd.Parameters.Add("as_phone_number", OracleDbType.VarChar, ParameterDirection.Input).Value = searchModel?.PhoneNo;

            cmd.Parameters.Add("as_postal_code", OracleDbType.VarChar, ParameterDirection.Input).Value = searchModel?.PostalCode;

            cmd.Parameters.Add("as_contact_name", OracleDbType.VarChar, ParameterDirection.Input).Value = searchModel?.ContactName;

            cmd.Parameters.Add("an_resp_user_nr", OracleDbType.Raw, ParameterDirection.Input).Value = searchModel?.ResponsibleUserId;

            cmd.Parameters.Add("as_wildcard_search", OracleDbType.VarChar, ParameterDirection.Input).Value = string.IsNullOrEmpty(searchModel?.Filter)? "%": searchModel.Filter;

            cmd.Parameters.Add("an_offset", OracleDbType.Integer).Value = 0;

            cmd.Parameters.Add("an_fetch", OracleDbType.Integer).Value = 12;

 

cmd.Parameters.Add(new OracleParameter

            {

               Direction = ParameterDirection.Input,

                OracleDbType = OracleDbType.Raw,

                ParameterName = "atbl_client_nr",

                Size = clientIds.Count,

                Value = clientIds.ToArray()

            });

            return cmd;

        }

Oracle Functions:

TYPE tbl_primary_key IS TABLE OF clients.id%TYPE INDEX BY BINARY_INTEGER; -- Here Clients is the table with id defined as Raw(16)- GUID

 

FUNCTION sf_empty_table_param RETURN tbl_primary_key;

 

 

FUNCTION sf_get_clients_query( as_name                  IN VARCHAR2                              DEFAULT NULL

                             , as_phone_number          IN VARCHAR2                              DEFAULT NULL

                             , as_address               IN VARCHAR2                              DEFAULT NULL

                             , as_postal_code           IN VARCHAR2                              DEFAULT NULL

                             , as_contact_name          IN VARCHAR2                              DEFAULT NULL

                             , an_resp_user_nr          IN clients.responsible_trader_id%TYPE    DEFAULT NULL -- responsible_trader_id is Raw(16) -GUID

                             , as_check_active_only     IN VARCHAR2                              DEFAULT 'Y'

                             , an_company_external_id   IN companies.external_id%TYPE            DEFAULT NULL -- external_id is Raw(16) -GUID

                             , atbl_client_nr           IN tbl_primary_key                       DEFAULT sf_empty_table_param

                             , as_wildcard_search       IN VARCHAR2                              DEFAULT NULL

                             , an_offset                IN NUMBER                                DEFAULT NULL

                             , an_fetch                 IN NUMBER                                DEFAULT NULL

                             ) RETURN SYS_REFCURSOR;

​

Oracle Table:

Name

Type

Optional

Default

Comments

ID

RAW(16)

 

 

 

BRANCH_OFFICE_ID

RAW(16)

Y

 

 

IS_ASSURED

NUMBER(1)

 

 

 

IS_SUSTAINABLE

NUMBER(1)

 

 

 

NAME

NVARCHAR2(300)

 

 

 

RESPONSIBLE_TRADER_ID

RAW(16)

Y

 

 

SHORT_NAME

NVARCHAR2(144)

 

 

 

EXTERNAL_ID

RAW(16)

 

 

 

SOURCE_DATE

TIMESTAMP(6) WITH TIME ZONE

 

 

 

DATE_UPDATED

TIMESTAMP(6) WITH TIME ZONE

 

 

 

LOGIN_ID

NVARCHAR2(24)

 

 

 

REC_STATUS

NUMBER(10)