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.
See Figure 3-20, which highlights the areas of the screen.
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:
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
where
and enter Ctrl+Space after the where
clause, you are provided with the list of fields of the Contact
object 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.Select
and from
in the query above, the query builder will display S
, suggesting that the S
should come in between Select
and From
, as shown here - Select S from ServiceCategory S
where
.S
, all relationships associated with Service
Category
are displayed.Select S from ServiceCategory S where S
, if you enter a dot after the last S
, all fields associated with ServiceCategory
will 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 Contact
, enter 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 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, Lin
and 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.
Figure 3-21 Query Inputs 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.
Figure 3-22 Query Example using Bind Parameters
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-24 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.