Working with ROQL

The RightNow Object Query Language (ROQL) facility enables you to make queries against the RightNow Cx database.

This section includes the following information:

Using ROQL

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.

ROQL Query Statement

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.

ROQL Query Statement

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.

Running the ROQL Query Statement

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.

Using the ROQL Query Builder

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:

Simplifying the Discovery of Standard and Custom Business Objects

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:

Select ROQL as the operation type on the Operations screen.
Enter Select in the ROQL Editor Text area and enter Ctrl+Space. The Query Builder displays a list of standard and custom objects available on Oracle RightNow Cx.

Note:

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.
Alternatively, you can provide a specific package to display all custom objects created in that package.

Suggesting Query Constructs Based on the Context

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:

When you enter Select in the ROQL query text area and press Ctrl+Space, the Query Builder displays all standard and business objects.
Similarly, if you perform the query 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.

Providing Auto-Completion

The auto-completion feature facilitates building complex ROQL queries in a quick and efficient manner. To see this feature:

Using the query string Select Se, if you perform Ctrl+Space after the term, Se, the Query Builder displays all objects that start with Se.

Providing Alias Support to Improve Readability

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.

In the query string 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.
If you perform Ctrl+Space between 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.
If you enter a dot after the first S, all relationships associated with Service Category are displayed.
Similarly in this query, Select S from ServiceCategory S where S, if you enter a dot after the last S, all fields associated with ServiceCategory will be displayed.

Using ROQL to Suggest Relationships

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.

Using the Query Builder to Obtain Inline and Semantic Validation

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.

Adapter Support for ROQL

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.

Query Arguments

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.

Support for Bind Parameters

The Oracle RightNow adapter provides you an option to give inputs to your query statement in the form of bind parameters.

Description of the illustration GUID-DA6BC9E9-CA01-4CF3-A38F-B70AB232D9BC-default.png

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

Description of "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.