Specify QueryCSV Statements When Configuring the Oracle Service Cloud (RightNow) Adapter as an Invoke

You can use the Oracle Service Cloud (RightNow) Adapter to execute tabular QueryCSV statements in Oracle Service Cloud (RightNow). You can use QueryCSV only when configuring the Oracle Service Cloud (RightNow) Adapter as an invoke.

Specifying a QueryCSV Statement

1. Add an Oracle Service Cloud (RightNow) Adapter as an invoke in an integration.

   This starts the Adapter Endpoint Configuration Wizard.

2. On the Basic Info page, specify a name and optional description, then click Next.

3. From the Select an Operation Type list on the Operations page, select ROQL.

4. Select QueryCSV from the dropdown list.

5. Specify a query in the editor using the following syntax:

   SELECT field list from primary object [WHERE condition expression]”

   where:

   Syntax	Description
   field list	Specifies a list of one or more fields, separated by commas. The field specified must be a simple field (one with a scalar type). If the SELECT statement ends at a primary object, subobject, or relationship object, a fault is generated.
   primary object	Specifies the type of object you want to query.
   condition expression	The WHERE clause determines the rows and values against which to filter in the specified field list. If unspecified, the query retrieves all the rows in the object.

6. Click Test My Query.

   The first ten results of the query are displayed:
   
   Description of the illustration rightnow_querycsv.png
   

   You can also specify query parameters. Query parameters are defined by & followed by the variable name. For example:

   SELECT Status.ID,EndPoint FROM EventSubscription DESC limit 10 offset &offsetData 

Because of memory limitations in Oracle Integration, only the first 100 rows in the result are returned at runtime. To get the next row, you can use limit and offset as query parameters to return the next set of results.

• Assume you have the following type of query:

  SELECT Status.ID,EndPoint FROM EventSubscription DESC limit &limit offset &offsetData

  For the first query, if you set the value of &offsetData to 0 and the value of &limit to 20, you get the first 20 rows. For the second query, if you set the value of &offsetData to 21 and the value of &limit to 10, you get the next 10 rows, and so on.

  You must send the updated offset value every time for the next query.

• You can use the Use keyword in a query. For example, you can define USE to query a different database:

  USE OPERATIONAL; SELECT id, name.first, name.last FROM Contact WHERE ID =3

• You can use a regular expression in a query. For example:

  SELECT id FROM CO.MyObject WHERE LookupName REGEXP '^TEST.*$

• Field expressions and logical operators that are supported in Oracle Service Cloud (RightNow) are also supported in QueryCSV. For example:

  SELECT ID FROM Contact WHERE Contact.Name.First like 'Chris' AND Contact.Address.City='Bozeman'

• You can use an alias to make the query more readable:

  SELECT O.Name FROM Organization O

Unsupported Features

• The DESCRIBE query is not supported. For example:

  DESCRIBE Contact.Emails.EmailList

• Queries with * are not supported. For example:

  SELECT RoqlInformation.Concurrency.*, RoqlInformation.Maxjoinsize.* FROM SystemInformation;

• Merging of more then one query is not supported. For example:

  SELECT id, name.first, name.last FROM Contact WHERE ID=3; SELECT id, createdTime,updatedTime FROM Incident WHERE ID= 1;

• Chaining is not supported. For example:

  SELECT ID as '@MyID' FROM Contact WHERE Organization = @MyOrg LIMIT 1

• Functions are not supported. For example:

  select sysdate(), select date_add(date, units, interval, round); select curAdminUser()

Fault Scenarios

Poorly written queries may not execute. When poorly written queries fail to execute, a SOAP fault is returned that contains one of the following messages based on the query supplied:

• Poor performing query - aborting

• Poor performing query - blocked

• Poor performing query - too many rows examined

• Poor performing query - too much time taken

• 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

See Invoke Operation Page.