REST API for Oracle B2C Service

Run ROQL Tabular Queries

You can use RightNow Object Query Language (ROQL) tabular (QueryCSV) queries to perform searches in the REST API. ROQL tabular query support is available by using the queryResults resource.

Note:

When using ROQL tabular queries to execute queries against the operational database, a maximum of 20,000 rows can be returned in a single request. If the request generates more than 20,000 rows in the result, the first 20,000 rows will be returned. When using the replication or reporting database, a maximum of 100,000 rows can be returned in a single request.

This topic contains the following sections:

ROQL Tabular Queries

ROQL tabular queries have the following characteristics:

They use the queryResults resource with ROQL syntax:

https://your_site_interface/services/rest/connect/version/
queryResults/?query=semicolon-separated ROQL queries

They return an array of collections containing matching rows for each input query.
They support multiple, semicolon-separated ROQL queries in the query parameter.

Permissions

The PAPI_ROQL_PERMISSIONS_ENABLED hidden configuration specifies whether Connect ROQL will honor CRUD permissions (set on a profile level) on the objects it queries when using tabular queries.

The following message in a site/interface info log (this example being specific to the tasks object) is a warning that access has been granted but would not have been granted if the PAPI_ROQL_PERMISSIONS_ENABLED configuration was enabled on a site.

Description: ROQL Permissions are currently disabled (see config PAPI_ROQL_PERMISSIONS_ENABLED). Access was granted to tasks, but will be blocked when ROQL permissions are enabled, which may lead to unexpected future results. It is advised to review account profiles and roles that may execute this query to ensure all necessary object access is granted, or modify the query to only select data that aligns with the assigned role.

The enablement of this configuration may be required in future releases and advised that you take the required action.

Using Regular Expressions

Starting with the February 2017 release, regular expression queries are supported with ROQL. Regular expression queries use the REGEXP operator and have the properties shown in the following table.

Property	Description
Query type	Tabular only Note: Queries on long text fields aren't supported because of potential performance issues.
Database	Reporting only. You must include a `USE REPORT` statement in the query.
Connect Common Object Model (CCOM) version	1.3 and later
Maximum number of concurrent queries `(ROQL_CONCURRENT_REGEX_QUERY_LIMIT)`	5 (default = 0, disabled)
Maximum join size `(MAX_JOIN_SIZE)`	One-half of ROQL MAX_JOIN_SIZE (default = 2.5 million/5 million)

Note:

Regular expression queries are a controlled-availability feature. To enable REGEXP queries, the hidden ROQL_CONCURRENT_REGEX_QUERY_LIMIT configuration setting must be set to greater than 0 (maximum of 5). To have the feature enabled, file a request on Oracle Support or contact your Oracle account manager.

Regular expression query example

The following example shows the use of regular expression queries. This query finds email addresses that end in "m."

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=USE REPORT;SELECT id, emails.address FROM accounts WHERE emails.address REGEXP 'm$' LIMIT 3 ;

Regular expression response example

Three results are returned:

{
    "items": [
        {
            "tableName": "accounts",
            "count": 3,
            "columnNames": [
                "id",
                "address"
            ],
            "rows": [
                [
                    "7",
                    "alarson@osvc.com"
                ],
                [
                    "10",
                    "bfoster@osvc.com"
                ],
                [
                    "22",
                    "rparker@osvc.com"
                ]
            ]
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=USE%20REPORT;SELECT%20id,%20emails.address%20FROM%20accounts%20WHERE%20emails.address%20REGEXP%20%27m$%27%20LIMIT%203%20;"
        },
        {
            "rel": "canonical",
            "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults"
        },
        {
            "rel": "describedby",
            "href": "https://mysite.example.com/services/rest/connect/v1.4/metadata-catalog/queryResults",
            "mediaType": "application/schema+json"
        }
    ]
}

Finding regular expression query limits

Use the following query to find the maximum number of concurrent queries and the maximum join size:

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=SELECT RoqlInformation.Concurrency.*, RoqlInformation.Maxjoinsize.* FROM SystemInformation;

The response shows the concurrencyLimit and rowLimit values:

{
  "items": [
    {
      "tableName": "systemInformation",
      "count": 1,
      "columnNames": [
        "condition",
        "concurrencyLimit",
        "condition",
        "rowLimit"
      ],
      "rows": [
        [
          "REGEXP",
          "5",
          "REGEXP",
          "2500000"
        ]
      ]
    }
  ],
  "links": [
    {
      "rel": "self",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults?
       query=SELECT%20RoqlInformation.Concurrency.*,%20RoqlInformation.Maxjoinsize.*%20FROM%20SystemInformation;"
    },
    {
      "rel": "canonical",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults"
    },
    {
      "rel": "describedby",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/metadata-catalog/queryResults",
      "mediaType": "application/schema+json"
    }
  ]
}

Error messages

The following errors from regular expression queries are logged to the info_log.xml file, and to the trace files if tracing is enabled:

"Current site configuration settings prevent execution of ROQL queries with the REGEXP operator."
"Too many ROQL queries with the REGEXP operator are running concurrently. Try again later."

ROQL Tabular Query Example

Using GET with the following query:

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=select id, displayName, profile.lookupName from accounts;

returns the following list of incidents:

{
    "items": [
        {
            "tableName": "accounts",
            "count": 18,
            "columnNames": [
                "id",
                "displayName",
                "lookupName"
            ],
            "rows": [
                [
                    "1",
                    "Administrator",
                    "Full Access"
                ],
                [
                    "3",
                    "Lucy Bauer",
                    "Marketing Manager"
                ],
                [
                    "4",
                    "Andrew Larson",
                    "Sales Rep"
                ],

                ...
                [
                    "18",
                    "Edward Lang",
                    "Service Manager"
                ]
            ]
        }
    ],
    "links": [
        {
            "rel": "self",
            "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=select%20id,%20displayName,%20profile.lookupName%20from%20accounts;"
        },
        {
            "rel": "canonical",
            "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults"
        },
        {
            "rel": "describedby",
            "href": "https://mysite.example.com/services/rest/connect/v1.4/metadata-catalog/queryResults",
            "mediaType": "application/schema+json"
        }
    ]
}