Bookshelf Home | Contents | Index | PDF |
Oracle Web Services On Demand Guide > Web Services On Demand API Calls > Web Services v2.0 API Calls > QueryPageExecutes a query against the set of records for an object, and returns the subset of the records that match the search criteria set by the method arguments. NOTE: For fields of all data types apart from xsd:string, if the field has a blank or no value, it does not appear in the query response. Team and Role Visibility ConsiderationsIf a query includes a child object for which the user does not have access due to team or role visibility, then the child object returns no records. Query by TemplateTo improve performance, the QueryPage result contains only those fields and objects that are included in the QueryPage request. To retrieve the values of fields that are not a part of the search criteria, the field must be included in the search request with a blank value. Using the pagesize, startrownum, and recordcountneeded ArgumentsThe pagesize argument, which has a maximum value of 100, is used to specify the maximum number of records to be returned in a QueryPage response. The pagesize and startrownum arguments are specified as attributes of the ListOf(Object) element in requests, for example: <ListOfAccount pagesize="20" startrownum="0" recordcountneeded="true"> Depending on the value of pagesize, records are returned as follows:
Even though the QueryPage method returns a limited number of records, it keeps the data in the cache, which you can then retrieve by calling the QueryPage method again with a new value for the startrownum argument. If you set the recordcountneeded argument to
NOTE: There is a performance impact if recordcountneeded is set to Using Search SpecificationsYou can use the searchspec argument of QueryPage to return only those records matching specified search criteria for an object. You can also specify the sort order and sort sequence for returned records using the sortorder and sortsequence arguments respectively. For more information, see Specifying the Sort Order and Sort Sequence. The searchspec argument is specified as an attribute of an Object element in requests, for example: <Contact searchspec="[ContactFirstName] = 'Jo*'"> which would return only contact records for which the ContactFirstName field value begins with Jo. The search specification can be set on any field type. The query syntax is described in the following topic. Query SyntaxThe query syntax for the searchspec argument supports only a small subset of binary and unary operators. No Siebel Query Language constructs or functions are supported. The query syntax is summarized in Table 34. CAUTION: Queries that are case-insensitive or with leading wildcard characters will perform slowly and must only be used when necessary. In some cases, you can however use shadow fields, see Using Shadow Fields for Better Search Performance. The following is an example from a request that follows the query syntax: <Contact searchspec="[ContactFirstName] = 'John'"> where XML Tag is Contact FirstName, the operator is = and the value is the literal value John. An example of an expression with a conjunction is as follows: <Contact searchspec="([ContactFirstName] = 'Jane'") AND ([ContactLastName] = 'Doe'")> Examples of searchspec usage are given in Table 35. Using Shadow Fields for Better Search PerformanceTo provide better performance for case-insensitive wildcard searches, you can use shadow fields in search specifications for some record types. Shadow fields are predefined and are found in the WSDL file for each object for which they are supported. Shadow fields add a suffix, _Shadow, to the actual field name. For example, for a Name field there would be a Name_Shadow field. Shadow fields always store their value in upper case, so, for example, if the Name field has the value To perform case insensitive searches, you use the shadow field instead of the actual field. For example, instead of a searchspec like this: searchspec="[Name] ~LIKE '%cle" you can use the following searchspec including upper case characters for better performance: searchspec="[Name_Shadow] LIKE '%CLE'" The EXISTS and NOT EXISTS OperatorsYou can use the unary operators EXISTS and NOT EXISTS with the searchspec argument of the QueryPage operation for any multivalued field of objects supported for Web Services v2.0. EXISTS and NOT EXISTS only work for multivalued fields and not on other fields, including multi-select picklists. Very few multivalued fields are available, but one example is the address fields on the Account and Contact parent objects. Multivalued fields contain data from multiple records, in the case of the address fields, multiple countries, counties, and so on. EXISTS returns true if the field expression matches for at least one of the values of the multivalued field. For example, to query for all contacts where any one of the values of the AlternateAddressExternalSystemId field for all associated addresses of Contact is <Contact searchspec="EXISTS ([AlternateAddressExternalSystemId] = 'XYZ')"> NOT EXISTS returns true if the field expression matches for none of the values of the multivalued field. For example, to query for all contacts where none of the values of the AlternateAddressExternalSystemId field for all associated addresses of Contact is <Contact searchspec="NOT EXISTS ([AlternateAddressExternalSystemId] = 'XYZ')"> As another example, to query for all accounts where any one of the values of the PrimaryBillToCounty field is Suffolk and any one of the values of the Primary Bill To Street Address field contains Ipswich: <Account searchspec="EXISTS ([PrimaryBillToCounty] = 'Suffolk' AND [PrimaryBillToStreetAddress] LIKE '%Ipswich%') Specifying the Sort Order and Sort SequenceYou can specify the sort order and sort sequence for returned records using the sortorder and sortsequence arguments respectively. The sortorder and sortsequence arguments are specified as attributes of a FieldName element in requests, for example: <CampaignName sortorder="ASC" sortsequence="1"/> which specifies that the records returned are sorted on the CampaignName field in an ascending order. The sortsequence argument is used to specify the order in which sorting is applied if a sortorder value is specified on more than one field, for example: <CampaignName sortorder="ASC" sortsequence="1"/> <Status sortorder="ASC" sortsequence="2"/>" Examples of sortorder and sortsequence usage are given in Table 36. NOTE: The sortorder argument is not supported for fields of date type CLOB or BLOB. Such fields could be used with the searchpsec argument with a LIKE operator, however, for performance reasons, use of CLOB and BLOB fields even for filtering must be avoided. CAUTION: Using multiple sort criteria might affect query performance. Use it only when necessary. Specifying Books in QueriesFor queries on record types that support books, you can use the BookId or BookName arguments to constrain the query to only the records in a particular book. For more information about the use of books, see Oracle CRM On Demand Online Help. Querying ListsYou can use the optional NamedSearchSpec argument to query a list. NamedSearchSpec can specify the name of a System, Public or Private list, however, a user cannot specify the name of a list to which he or she does not have access in the UI. The set of fields to be returned must be included in the SOAP request. The set of fields defined in the list as well as the sort order of the list are not used to define the response, however, the query uses the filter criteria defined in the UI for the list. You can use NamedSearchSpec together with the searchspec argument to search within a list. In a QueryPage request when NamedSearchspec and field-level searchspecs are specified, an AND operation is performed between NamedSearchspec and the field-level searchspecs. For example, if the searchspec in a request is: and the value of NamedSearchSpec is: ([Field1] > 100 OR ([Field2] > 500 AND [Field 3] = true) AND [Field4] = 'MyName') AND [Field5] = 'Open' then the effective searchspec is: ([Field]=500) AND (([Field1] > 100 OR ([Field2] > 500 AND [Field 3] = true) AND [Field4] = 'MyName') AND [Field5] = 'Open') Specifying Proximity Search ParametersIn the Maps page in Oracle CRM On Demand you can perform a proximity search to check the addresses on records of a given record type to determine whether the addresses are within a specified radius of a selected location. Proximity searches are supported for the Account, Activity, and Contact record types. In the QueryPage methods for these record types, you must use the following arguments to specify the parameters for a proximity search:
The namedsearchspec argument is used to define lists of records on which proximity searches can be made. If the arguments are missing or incorrect, an error message is received. For more information about proximity searching, see Oracle CRM On Demand Online Help. ArgumentsTable 37 describes the arguments taken by the QueryPage method.
Return Value of the CallAn object or list of objects of the type on which the method was called, with attributes as follows:
Sample SOAP RequestsThis topic contains a number of sample SOAP requests and responses to illustrate the use of the QueryPage API. Sample SOAP Request 1 - QueryPage with Field-level FilterThe following SOAP request queries for contact records that have a ContactType like <?xml version="1.0" encoding="utf-8" standalone="no"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <wsse:Username>%%USERNAME%%</wsse:Username> <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">%%PASSWORD%%</wsse:Password> <ContactQueryPage_Input xmlns="urn:crmondemand/ws/ecbs/contact/"> <LOVLanguageMode>LIC</LOVLanguageMode> <ListOfContact pagesize="100" startrownum="0" recordcountneeded="true"> <ContactType>LIKE 'Customer*'</ContactType> Sample SOAP Response 1The following SOAP response shows the list of 5 objects returned by SOAP request 1. <?xml version="1.0" encoding="utf-8"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ns:ContactQueryPage_Output xmlns:ns="urn:crmondemand/ws/ecbs/contact/"> <ListOfContact xmlns="urn:/crmondemand/xml/Contact/Data" recordcount="5" lastpage="true"> <ContactFirstName>D</ContactFirstName> <ContactLastName>Dan</ContactLastName> <ContactType>Customer</ContactType> <ListOfActivity lastpage="true"> <Subject>Send D Dan welcome email</Subject> <ContactFirstName>L</ContactFirstName> <ContactLastName>Lak</ContactLastName> <ContactType>Customer</ContactType> <ListOfActivity lastpage="true"> <Subject>Send L Lak welcome email</Subject> <ContactFirstName>NewCon6</ContactFirstName> <ContactLastName>Con6</ContactLastName> <ContactType>Customer</ContactType> <ListOfActivity lastpage="true"> <Subject>Add activity to C Contacts</Subject> <ContactEmail>n@l.com</ContactEmail> <ContactFirstName>Nathan</ContactFirstName> <ContactLastName>Lak</ContactLastName> <ContactType>Customer</ContactType> <ListOfActivity lastpage="true"> <Subject>Send Nathan Lak welcome email</Subject> <Subject>Send Jordan Lak welcome email</Subject> <Subject>Send J Lak welcome email</Subject> <Subject>ActivityInsert Test R19</Subject> <Subject>ActivityInsert Test R19 2</Subject> <Subject>ActivityInsert Test R19 2</Subject> <ContactFirstName>NewCon5</ContactFirstName> <ContactLastName>NewCon5</ContactLastName> <ContactType>Customer</ContactType> <ListOfActivity lastpage="true"></ListOfActivity> Sample SOAP Request 2 - QueryPage with searchspec, sortsequence and sortorder AttributesThe following SOAP request queries for Contact records by specifying a search criterion with an OR conjunction in the searchspec attribute. The samples also demonstrate use of the optional pagesize, startrownum, and recordcountneeded attributes at the <ListOfObject> level. In SOAP request 2a, pagesize = 5 and startrownum = 0, this request returns the 5 records starting at row 0 (first record) that match the search criteria. In SOAP request 2b, pagesize = 5 and startrownum = 5, this request returns the next 5 records starting at row 5 (sixth record) that match the search criteria. In addition, the sample demonstrates the sortsequence and sortorder attributes at the element level to specify the sort order and sort sequence of the whole result set (including rows of records not returned due to the pagesize limit). Note that across both responses, (SOAP response 2a and SOAP response 2b), the records are sorted accordinh to the specified sort sequence and sort order. Sample SOAP Request 2aIn this SOAP request, pagesize = 5 and startrownum = 0: <?xml version="1.0" encoding="utf-8" standalone="no"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <wsse:Username>%%USERNAME%%</wsse:Username> <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">%%PASSWORD%%</wsse:Password> <ContactQueryPage_Input xmlns="urn:crmondemand/ws/ecbs/contact/"> <ListOfContact pagesize="5" startrownum="0" recordcountneeded="true"> <Contact searchspec="[ContactLastName] LIKE 'C*' OR [ContactType] = 'Customer'"> <ContactFirstName></ContactFirstName> <ContactLastName sortorder="DESC" sortsequence="2"></ContactLastName> <ContactType sortorder="DESC" sortsequence="1"></ContactType> Sample SOAP Response 2aThe following SOAP response shows the list of 5 objects returned by SOAP request 2a: <?xml version="1.0" encoding="utf-8"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ns:ContactQueryPage_Output xmlns:ns="urn:crmondemand/ws/ecbs/contact/"> <ListOfContact xmlns="urn:/crmondemand/xml/Contact/Data" recordcount="54" lastpage="false"> <ContactFirstName>James</ContactFirstName> <ContactLastName>Conners</ContactLastName> <ContactType>Prospect</ContactType> <ContactFirstName>Ruth</ContactFirstName> <ContactLastName>Chou</ContactLastName> <ContactType>Prospect</ContactType> <ContactFirstName>Jason</ContactFirstName> <ContactLastName>Cheney</ContactLastName> <ContactType>Prospect</ContactType> <ContactFirstName>Sean</ContactFirstName> <ContactLastName>Thomas</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>Carlos</ContactFirstName> <ContactLastName>Santos</ContactLastName> <ContactType>Customer</ContactType> Sample SOAP Request 2bIn this SOAP request, pagesize = 5 and startrownum = 5 <?xml version="1.0" encoding="utf-8" standalone="no"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <wsse:Username>%%USERNAME%%</wsse:Username> <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">%%PASSWORD%%</wsse:Password> <ContactQueryPage_Input xmlns="urn:crmondemand/ws/ecbs/contact/"> <ListOfContact pagesize="5" startrownum="5" recordcountneeded="true"> <Contact searchspec="[ContactLastName] LIKE 'C*' OR [ContactType] = 'Customer'"> <ContactFirstName></ContactFirstName> <ContactLastName sortorder="DESC" sortsequence="2"></ContactLastName> <ContactType sortorder="DESC" sortsequence="1"></ContactType> Sample SOAP Response 2bThe following SOAP response shows the list of 5 objects returned by the request in SOAP request 2b: <?xml version="1.0" encoding="utf-8"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ns:ContactQueryPage_Output xmlns:ns="urn:crmondemand/ws/ecbs/contact/"> <ListOfContact xmlns="urn:/crmondemand/xml/Contact/Data" recordcount="54" lastpage="false"> <ContactFirstName>Nick</ContactFirstName> <ContactLastName>Ray</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>Joanna</ContactFirstName> <ContactLastName>Pressman</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>Marianne</ContactFirstName> <ContactLastName>Pok</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>Brian</ContactFirstName> <ContactLastName>Pittenger</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>Marie</ContactFirstName> <ContactLastName>Perkins</ContactLastName> <ContactType>Customer</ContactType> Sample SOAP Request 3 - QueryPage with NamedSearchSpecThe following SOAP request queries for contact records in a specific contact list by specifying the contact list name in the <NamedSearchSpec> element. Table 38 shows the records in the C Contacts list from the Oracle CRM On Demand UI. There are a total of 11 records in the list, therefore the recordcount value returned in the SOAP response equals 11.
<?xml version="1.0" encoding="utf-8" standalone="no"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <wsse:Username>%%USERNAME%%</wsse:Username> <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">%%PASSWORD%%</wsse:Password> <ContactQueryPage_Input xmlns="urn:crmondemand/ws/ecbs/contact/"> <NamedSearchSpec>C Contacts</NamedSearchSpec> <ListOfContact pagesize="5" startrownum="0" recordcountneeded="true"> Sample SOAP Response 3The following SOAP response shows the list of contacts returned by sample SOAP request 3. <?xml version="1.0" encoding="utf-8"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ns:ContactQueryPage_Output xmlns:ns="urn:crmondemand/ws/ecbs/contact/"> <ListOfContact xmlns="urn:/crmondemand/xml/Contact/Data" recordcount="11" lastpage="false"> <ContactFirstName>David</ContactFirstName> <ContactLastName>Cuthbery</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>Robin</ContactFirstName> <ContactLastName>Coxe</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>John</ContactFirstName> <ContactLastName>Connolly</ContactLastName> <ContactType>Customer</ContactType> <ContactFirstName>James</ContactFirstName> <ContactLastName>Conners</ContactLastName> <ContactType>Prospect</ContactType> <ContactFirstName>Alexander</ContactFirstName> <ContactLastName>Cone</ContactLastName> |
Oracle Web Services On Demand Guide, Version 20.0 (Oracle CRM On Demand Release 32) | Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Legal Notices. | |