The RightNow Object Query Language (ROQL) facility enables you to make queries against the RightNow Cx database.
This section includes the following information:
If you select the ROQL operation category on the Operations screen, a field for entering an ROQL query is displayed. Enter the ROQL statement depending on the operation you selected.
ROQL enables you to search your organization's RightNow Cx data for specific information.
This chapter does not focus on discussing ROQL at length. Refer to Oracle Service Cloud Connect Web Services for SOAP.
The ROQL query statement queries data from Oracle RightNow Cx. The Query statement text box consists of ROQL statements according to Oracle RightNow CX.
Refer to the section on ROQL Operations for more information on the query operation. For detailed information on ROQL, see the Oracle RightNow Connect Web Services for SOAP Developer's Guide and navigate to the ROQL section.
Use the ROQL query statement to query data from RightNow Cx. The Query statement text box consists of ROQL statements according to RightNow Cx.
Refer to the section in ROQL Operations for additional information on query operations. For detailed information on ROQL, see the Oracle RightNow Connect Web Services for SOAP Developer's Guide and navigate to the ROQL section.
You can run and test the query. When you click the Test icon, a test dialogue box appears.
The Query Test dialog that appears contains the following areas:
Query Statement and Results. The Query Statement text box is for the query itself and the Results box shows the result of the query executed. If the query has one or more bind parameters in it, one or more input boxes will appear to provide values for these
Parameter Bindings. Enter values for any parameter bindings that appear in the query statement.
Test My Query. Click to execute the query.
Results. After the successful execution of the query, the text area is filled with results from RightNow Cx.
This section discusses how to use the ROQL query builder. The ROQL query builder addresses several areas that are often problematic for the integration developer.
This approach helps you in writing complex, error-free queries with greater ease. To accomplish writing such queries, the ROQL Query Builder provides the following features, described in these subsections:
The ROQL Query Builder identifies for you potential standard and custom objects that you can use in constructing the queries. To see an example of this feature:
If the custom objects you create do not appear in the list of objects shown in the operations page, check the option
Clear Cache in the connection page, then connect again to obtain the latest metadata from the RightNow Cx system.
ROQL Query Builder automatically identifies the context, based on the position of the cursor, for the query string. Using this context, the Query Builder provides recommendations, enabling you to write efficient ROQL queries. This feature operates in the following manner:
Select Contact from Contact
whereand enter Ctrl+Space after the
whereclause, you are provided with the list of fields of the
Contactobject that you can use in fine-tuning the query, and applicable keywords and functions.
The auto-completion feature facilitates building complex ROQL queries in a quick and efficient manner. To see this feature:
Select Se, if you perform Ctrl+Space after the term,
Se, the Query Builder displays all objects that start with Se.
The ROQL Query Builder features support for aliasing. It recognizes when aliasing of the RightNow Object (Base class for all primary objects in the Oracle RightNow adapter) is being queried and treats the alias name as the actual object name.
All operations associated with the object can then be performed on the alias.
Select from ServiceCategory S where, S is set as an alias for object
ServiceCategory. Thus, you can perform all operations associated with ServiceCategory on the alias 'S'. Some of the operations you can perform on the alias include the following.
fromin the query above, the query builder will display
S, suggesting that the
Sshould come in between
From, as shown here -
Select S from ServiceCategory S
S, all relationships associated with
Select S from ServiceCategory S where S, if you enter a dot after the last
S, all fields associated with
ServiceCategorywill be displayed.
ROQL has support for the relationship feature. With this feature you can reach from one primary object to other primary objects through a relationship.
The ROQL Query Builder identifies and lists all reachable relationships from the identified object. It can list relationships of relationships.
For more details see Oracle RightNow Connect Web Services for SOAP Developer's Guide
If you must find the parent organization of a particular
Select Contact in the ROQL text area and enter "." which displays all relationships possible on the Contact object.
The Query Builder has a built-in inline validator that highlights problematic text by drawing a red curved line underneath it and also provides suggestions to correct the error in a tooltip message.
The Query Builder validator performs spelling checks on keywords, object names, relationship names, field names and names of functions. All the keywords that are typed correctly will automatically be colored blue, indicating the validity of these keywords.
The ROQL Query builder also has support for semantic validation of the query. It can recognize that the specified relationship or field name is invalid for the currently selected object.
The Oracle RightNow adapter provides a query subsystem that enables you to perform SQL-like queries against the RightNow CX platform using the object query language.
The ROQL query language is based on a subset of the Object Query Language (OQL) and a SQL-like query language that has been extended to handle object notation.
ROQL currently supports only the Query Objects Feature and not Query CS.
Using the following query as an example:
Use query arguments to fine-tune queries. Therefore, in the above example,
CA are query arguments.
Select Contact from Contact where Contact.Name.First = 'Lin' AND Contact.Address.City = 'CA'
Query parameters are a unique concept through you can parameterize the query arguments, which the adapter substitutes with actual values during query execution.
Much like SQL, the query language has a provision to fine-tune the search by passing in filters in the
WHERE clause. This fine-tuning is made possible by use of the concept of bind parameters.
The Oracle RightNow adapter provides you an option to give inputs to your query statement in the form of bind parameters.
See the following query example for the Oracle RightNow adapter. The ampersand
& symbol prefixed to
orgid indicates that the ampersand is a bind parameter.
You can run the query to see how it works with bind parameters by clicking Test My Query.
As shown above, the query prompts you to provide a value for the bind parameter.
The WSDL generated for an adapter query contains the bind parameter as part of input schema.
Figure 3-9 Part of Generated Input Schema for WSDL Showing Bind Parameter
The JCA file generated for an adapter query can look as follows:
Example 3-7 JCA File Generated when Bind Parameters are Used in Query
<adapter-config name="rightnowReferencePortType" adapter="rightnow" wsdlLocation="../WSDLs/rightnowReference.wsdl" xmlns="http://platform.integration.oracle/blocks /adapter/fw/metadata"> <connection-factory location= "cloud/CloudAdapter"> <non-managed-connection managedConnectionFactoryClassName="oracle.cloud.connector. rightnow. RightNowConnectionFactory"> <property name="targetWSDLURL" value="../WSDLs/soap.wsdl"/> <property name="csfkey" value="csf21"/> <property name="appID" value= "removeWhenAppIdIsHardcodedByRNPlugin"/> <property name="applicationVersion" value="1_2"/> </non-managed-connection> </connection-factory> <endpoint-interaction portType="rightnowReferencePortType" operation="Get"> <interaction-spec className="oracle.tip.adapter.cloud. CloudInteractionSpec"> <property name="targetOperation" value="Get"/> <property name="operationPath" value="Get"/> <property name="gpo.fetchAllNames" value="false"/> </interaction-spec> </endpoint-interaction> </adapter-config>
Once queries are parameterized, the Oracle RightNow adapter enables its consumers to pass in the query parameters as part of the request payload.
The adapter dynamically substitutes the query parameter from the request payload to reconstruct the ROQL query with arguments during service invocation.
Thus, by using the adapter to easily define query parameters, you can parameterize query arguments and dynamically inject these parameters at runtime.