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_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;

        }

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;

​