1. Service Cloud REST APIREST API for Oracle B2C Service
  2. Learn More
  3. Manage Collections
  4. Query ROQL Managed Tables

Query ROQL Managed Tables

RightNow Object Query Language (ROQL) managed tables allow integration developers to extract read-only data from OracleB2C Service tables that aren't yet exposed through the Connect Common Object Model (CCOM).

The following managed tables are available through the REST API:

AnswerStatistics

Answer statistics data allows API clients to extract a customer's statistical usage data for answers (knowledge articles), so that the data can be used in various reports and business intelligence tools. AnswerStatistics data is derived from the ans_stats table in Oracle B2C Service and is exposed as a subobject of a statsInformation object. The statsInformation object collectively exposes all of the statistical data in Oracle B2C Service.

Request URI example

The following example shows a ROQL query that retrieves all AnswerStatistics records from the statsInformation table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=SELECT AnswerStatistics.* FROM statsInformation;

Response body example

{
  "items": [
    {
      "tableName": "statsInformation",
      "count": 4,
      "columnNames": [
        "answer",
        "createdTime",
        "numberOfPageHits",
        "interface",
        "rank1Count",
        "rank2Count",
        "rank3Count",
        "rank4Count",
        "rank5Count"
      ],
      "rows": [
        [
          "3",
          "2010-09-17T16:00:00Z",
          "1",
          "1",
          "0",
          "0",
          "0",
          "0",
          "0"
        ],
        [
          "4",
          "2010-09-17T16:00:00Z",
          "1",
          "1",
          "0",
          "1",
          "0",
          "0",
          "0"
        ],
        [
          "5",
          "2010-09-17T16:00:00Z",
          "1",
          "1",
          "0",
          "0",
          "0",
          "0",
          "0"
        ],
        [
          "6",
          "2010-09-17T16:00:00Z",
          "2",
          "1",
          "1",
          "0",
          "0",
          "0",
          "0"
        ]
      ]
    }
  ],
  "links": [
    {
      "rel": "self",
      "href": "https://mysite.example.com/services/rest/connect/v1.4/queryResults?
       query=SELECT%20AnswerStatistics.*%20FROM%20statsInformation;"
    },
    {
      "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"
    }
  ]
}

ApplicationTransactions

The Application Transaction support allows API clients to extract application transaction information that can be used to link events pertaining to the same transaction.

Request URI example

The following example shows a ROQL query that retrieves metadata about the applicationTransactions table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe applicationTransactions;

The following example shows ROQL query that retrieves all the application transaction information for transactions that were created by a specific account

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query= select * from applicationTransactions where createdByAccount.id = 3;

ArchivedIncidents

Archived incident support allows API clients to retrieve information about archived incidents, prevent entities being added to a whitelist, and avoid direct SQL queries.

Archived incident data is read from the archived_incidents table.

Request URI example

The following example shows a ROQL query that retrieves metadata about the archived_incidents table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe archivedincidents;

ChatQueues

ChatQueues support allows API clients to retrieve information about chat queues, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the chat_queues table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe chatQueu

CustomObjectTransactions

CustomObjectTransactions allows API clients to retrieve transaction data on custom objects, so that the data can be used in various reports and business intelligence tools. Custom object transactions data is derived from the Co_trans table in Oracle B2C Service and is exposed as a transactions subobject under the corresponding custom object. The transactions subobject is a part of the OSvC reserved package and is accessed as OSvC.Transactions. You can extract transactional information from the following fields:

  • account: account that performed the transaction
  • operation: such as Create or Edit
  • operationTime: datetime when the transaction was performed

Request URI example

The following example shows a ROQL query that retrieves transaction information from a custom object:

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=SELECT ID, OSvC.Transactions.* FROM CustomObjec.Custom1;

DeletedRecordInformation

Deleted record information support allows API clients to extract deleted records from the Oracle B2C Service database.

The deleted_recs table holds six DeletedRecordInformation subobjects.

Request URI example

The following example shows a ROQL query that retrieves metadata about the deleted_recs table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe deletedRecordInformation;

DistributionEmailAddresses

Distribution email address support allows API clients to retrieve information about distribution lists, prevent entities being added to a whitelist, and avoid direct SQL queries.

Distribution email address data is read from the mail_addrs table.

Request URI example

The following example shows a ROQL query that retrieves metadata about the mail_addrs table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe distributionEmailAddresses;

DistributionEmailLists

Distribution email list support allows API clients to retrieve information about distribution lists, prevent entities being added to a whitelist, and avoid direct SQL queries.

Distribution email list data is read from the mail_lists table.

Request URI example

The following example shows a ROQL query that retrieves metadata about the mail_lists table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe distributionEmailLists;

Flows

Flows support allows API clients to retrieve information about flows in Marketing and Feedback, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the flows table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe flows;

FlowQuestions

FlowQuestions support allows API clients to retrieve information about mapping survey questions to flows in Marketing and Feedback, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the flow2question table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe flowQuestions;

IncidentQueues

IncidentQueues support allows API clients to retrieve information about incident queues, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the incident_queues table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe incidentQueues;

PerformanceRecords (Incident)

Performance record support allows API clients to extract incident performance data for use in various reporting and business intelligence applications. Performance records are derived from the inc_performance table in Oracle B2C Service and are a subobject of the incidents primary object.

Request URI example

The following example shows a ROQL query that retrieves all records from the inc_performance table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=select performancerecords.* from incidents where performancerecords.startTime is not null;

Note:

The IS NOT NULL filter is necessary because not all incidents have PerformanceRecords entries. ROQL performs left-outer joins on each incident that doesn't have a PerformanceRecords entry and will therefore generate a row unless the IS NOT NULL filter is added.

PerformanceRecords (Opportunity)

Performance record support allows API clients to extract opportunity performance data for use in various reporting and business intelligence applications. Performance records are derived from the opp_performance table in Oracle B2C Service and are a subobject of the opportunities primary object.

Request URI example

The following example shows a ROQL query that retrieves all records from the opp_performance table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=select performancerecords.* from opportunities where performancerecords.startTime is not null;

Note:

The IS NOT NULL filter is necessary because not all opportunities have PerformanceRecords entries. ROQL performs left-outer joins on each opportunity that doesn't have a PerformanceRecords entry and therefore generates a row unless the IS NOT NULL filter is added.

Profile2Queue

Profile2Queue allows API clients to retrieve information connecting profiles to queues. It is exposed as a subobject of Profiles:

  • Profile2ChatQueue: to retrieve information connecting chat queues to profiles
  • Profile2IncidentQueue: to retrieve information connecting incident queues to profiles

You can extract information from the following fields:

  • Priority
  • Queue
  • Responsibility

Request URI example

The following example shows a ROQL query that retrieves all the incident queues associated to profiles:

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=SELECT IncidentQueues.* FROM Profiles;

The following example shows a ROQL query that retrieves the metadata for Profile2ChatQueue:

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=Describe Profiles.ChatQueues.ProfilesToChatQueueList;

Profiles

Profiles support allows API clients to retrieve information about staff profiles, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the profiles table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe profiles;

SalesStages

A sales stage is the level of completion of an opportunity within a sales strategy. Sales stage support allows API clients to read data from the sa_stages table to retrieve information about sales stages created in the Opportunity Tracking Service.

Request URI example

The following example shows a ROQL query that retrieves metadata about the sa_stages table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe salesstages;

SLAs

A service level agreement (SLA) is a contract that's applied to contacts and organizations specifying the level and type of customer service they are eligible to receive. SLA support allows API clients to read data from the slas table to retrieve information about service level agreements, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the slas table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe slas;

SocialQuestionCommentContentRatingSummaries

This managed table contains aggregated ratings for social comment content that you can use to display the average ratings of comment content at any time without any calculation. It's a running total of all of the ratings for content. SocialQuestionCommentContentRatingSummaries is a read-only object that can be accessed only from ROQL through its parent object socialQuestionComments.

Request URI example

The following example shows a ROQL query that retrieves all ContentRatingSummaries records from the socialQuestionComments table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=SELECT ContentRatingSummaries.* FROM socialQuestionComments;

SocialQuestionContentRatingSummaries

This managed table contains aggregated ratings for social question content that you can use to display the average ratings of question content at any time without any calculation. It's a running total of all of the ratings for content. SocialQuestionContentRatingSummaries is a read-only object that can be accessed only from ROQL through its parent object socialQuestions.

Request URI example

The following example shows a ROQL query that retrieves all ContentRatingSummaries records from the socialQuestions table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=SELECT ContentRatingSummaries.* FROM socialQuestions;

Surveys

Survey support allows API clients to retrieve information about surveys, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the surveys table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe surveys;

SurveyQuestions

Survey question support allows API clients to retrieve information about survey questions, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the surveyQuestions table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe surveyQuestions;

SurveyQuestionResponses

Survey question response support allows API clients to retrieve information about responses to surveys, prevent entities being added to a whitelist, and avoid direct SQL queries.

SurveyQuestionResponses is accessed through its parent table, surveySessions.

Request URI example

The following example shows a ROQL query that retrieves all responses from the surveySessions table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=SELECT Responses.* FROM surveySessions;

SurveySessions

Survey session support allows API clients to retrieve information about survey sessions, prevent entities being added to a whitelist, and avoid direct SQL queries.

Request URI example

The following example shows a ROQL query that retrieves metadata about the surveySessions table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe surveySessions;

TaskTemplates

Task template support allows API clients to retrieve information about task templates associated with sales strategies created in the Opportunity Tracking Service. Task templates are derived from the task_templates table in Oracle B2C Service.

Request URI example

The following example shows a ROQL query that retrieves metadata about the task_templates table: 

https://mysite.example.com/services/rest/connect/v1.4/queryResults?query=describe tasktemplates;

Transactions

Transaction support allows API clients to extract system-level audit data, such as transaction logs, user activity logs, and incident performance data on contact, incident, opportunity, organization, and task objects. With transaction support, you can extract audit data from Oracle B2C Service and use it in various reporting and business intelligence applications. You can extract transactional information from the following fields:

  • account: account that performed the transaction
  • operation: such as Create or Edit
  • operationTime: datetime when the transaction was performed

Request URI example

The following example shows a ROQL query that retrieves transaction information from a contacts object:

https://mysite.example.com/services/rest/connect/v1.4/queryResults?
query=SELECT transactions.* FROM contacts WHERE id=2;