X

Best Practices from Oracle Development's A‑Team

Integrating Oracle Service Cloud (RightNow) with Oracle Business Intelligence Cloud Service (BICS) - Part 2

Jay Pearson
Consulting Solutions Architect, Business Intelligence

Introduction

This article expands on "Integrating Oracle Service Cloud (RightNow) with Oracle Business Intelligence Cloud Service (BICS) - Part 1" published in June 2015.

Please Note - this article is intended to provide the reader with some ideas of how to start an integration with Service Cloud.  The reader would also need to consider other factors such as Limits, Fault Tolerance, Scalability, Efficiency, Latency, etc which are common with any enterprise integration.  This article is intended to be part of that overall integration, and not the only piece.  Please consult with an integration expert.

Part 1 described how to integrate BICS with Oracle Service Cloud (RightNow) Connect Web Services for Simple Object Access Protocol (SOAP).

Part 2 covers using the Connect REST API which allows integration with Oracle Service Cloud (RightNow) using representational state transfer (REST) web services.

REST is currently the recommended and suggested method for integrating BICS with Oracle Service Cloud (RightNow). The Connect REST API has been available as of the May 2015 release of Oracle Service Cloud (RightNow). However, support for ROQL object and tabular queries only became available in the August 2015 release. Therefore, Oracle Service Cloud (RightNow) August 2015 or higher is required to implement the examples provided.

Additionally, this article showcases the new APEX_JSON package that is available as of Oracle Apex 5.0 for parsing and generating JSON. The following three APEX_JSON functions are utilized in the solution: apex_json.parse, apex_json.get_number and, apex_json.get_varchar2.

The eight steps below explain how to load data from Oracle Service Cloud (RightNow) into BICS using PL/SQL ran through Oracle Apex SQL Workshop - using an Oracle Schema Service Database. The code snippets may then be incorporated into a stored procedure or web service and scheduled / triggered. (Such topics have been covered in past BICS blogs.)

1)    Construct the ROQL Query

2)    Test the Connect REST API Query URL

3)    Run the apex_web_service.make_rest_request

4)    Formulate the JSON Path Expression

5)    Create the BICS Database Table

6)    Add the APEX_JSON parse code to the PL/SQL

7)    Execute the PL/SQL

8)    Review the Results

 

Main Article

 

Step One - Construct the ROQL Query

 

See Step Two of "Integrating Oracle Service Cloud (RightNow) with Oracle Business Intelligence Cloud Service (BICS) - Part 1" on how to construct the ROQL Query. For this example the ROQL Query is:

select id, subject from incidents where id <12500

Step Two - Test the Connect REST API Query URL

 

This section covers building and testing the URL for ROQL object queries.

The q query parameter ROQL syntax is as follows:

https://<your_site_interface>/services/rest/connect/<version>/<resource>/?q=<ROQL Statement>

1)    Take the ROQL Query from Step One and replace all the spaces with %20.

For example: select%20id,%20subject%20from%20incidents%20where%20id%20<12500

2)    Append the ROQL string to the REST API URL. For example:

https://yoursite.rightnowdemo.com/services/rest/connect/v1.3/queryResults/?query=select%20id,%20subject%20from%20incidents%20where%20id%20<12500

3)    Place the URL into a browser. It should prompt for a username and password. Enter the username and password.

4)    The browser will then prompt to save or open the results. Save the results locally. View the results in a text editor.

The text file should contain the results from the ROQL query in JSON format.

Snap1

 

 

 

Step Three - Run the apex_web_service.make_rest_request

1)    Open SQL Workshop from Oracle Application Express

Snap2

 

 

2)    Launch SQL Commands

Snap3

 

 

3)    Use the snippet below as a starting point to build your PL/SQL.

Run the final PL/SQL in the SQL Commands Window.

Replace the URL site, username, password, and ROQL query.

For a text version of the code snippet click here.

DECLARE
l_ws_response_clob CLOB;
l_ws_url           VARCHAR2(500) := 'https://yoursite.rightnowdemo.com/services/rest/connect/v1.3/queryResults/?query=select%20id,%20subject%20from%20incidents%20where%20id%20<12500';
--
apex_web_service.g_request_headers(1).name := 'Accept'; apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8'; apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_username => 'username',
p_password => 'password',
p_http_method => 'GET'
);
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,12000,1));
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,12000,12001));
dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,12000,24001));
--

4)    Run the query. A subset of the JSON results should be displayed in the Results section of SQL Commands.

Additional dbms_output.put_line's may be added should further debugging be required.

It is not necessary at this stage to view the entire result set. The key to this exercise is to prove that the URL is correct and can successfully be ran through apex_web_service.make_rest_request.

Snap4

 

 

Step Four - Formulate the JSON Path Expression

 

1)    When formulating the JSON path expression, it may be useful to use an online JSON Path Expression Tester.

There are many different free JSON tools available online. The one below is: https://jsonpath.curiousconcept.com/

When testing, reduce the JSON output to something more manageable.

For a text version of the JSON used in this example click here.

Snap5

 

 

2)    For this exercise the following needs to be exacted from the JSON: count, all the ids, and all the subject data.

Test various path scenarios to confirm that the required data can be extracted from the JSON.

(It is much easier to debug the path in an online JSON editor than in SQL Developer.)

a)    COUNT

JSONPath Expression:

items[0].count

Returns:

[
3
]

b)    ID

id #1

JSONPath Expression:

items[0]["rows"][0][0]

Returns:

[
"12278"
]

id #2

JSONPath Expression:

items[0]["rows"][1][0]

Returns:

[
"12279"
]

id #3

JSONPath Expression:

items[0]["rows"][2][0]

Returns:

[
"12280"
]

c)    SUBJECT

subject #1

JSONPath Expression:

items[0]["rows"][0][1]

Returns:

[
"How long until I receive my refund on my credit card?"
]

subject #2

JSONPath Expression:

items[0]["rows"][1][1]

Returns:

[
"Do you ship outside the US?"
]

subject #3

JSONPath Expression:

items[0]["rows"][2][1]

Returns:

[
"How can I order another product manual?"
]

 

Step Five -  Create the BICS Database Table

 

1)    Open SQL Workshop from Oracle Application Express

Snap2

 

 

 

2)    Launch SQL Commands

Snap3

 

 

 

 

 

 

 

 

3)    Create the RIGHT_NOW_REST table in the BICS database.

To view the SQL in plain text click here.

CREATE TABLE "RIGHT_NOW_REST"
(ID NUMBER,
SUBJECT VARCHAR2(500)
);

Snap7

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step Six - Add the APEX_JSON parse code to the PL/SQL

 

For a text version of the PL/SQL snippet click here.

The code highlighted in orange is what was tested in 'Step Three - Run the apex_web_service.make_rest_request'.

Replace the URL site, username, password, and ROQL query, as previously described in 'Step Three - Run the apex_web_service.make_rest_request'.

The objective of this step is to add the JSON Path Expression's formulated and tested in Step Four into the PL/SQL.

Note: Three debug lines have been left in the code - as they will most likely be needed. These can be removed if desired.

The key parts to the APEX_JSON code are:

1)    Identify a value that can be used to loop through the records.

Apex PL/SQL: apex_json.get_number(p_path => 'items[1].count')

In this example it is possible to use "count" to loop through the records.

However, different use cases may require another parameter to be used.

Note the different numbering in the JSONPath Expression vs. the apex_json function.

In the online JSONPath Expression builder position 1 = [0]. However, in Apex position 1 = [1].

Snap8

 

 

2)    Map the fields to be selected from the JSON.

ID:             apex_json.get_varchar2(p_path => 'items[1].rows['|| i || '][1]'),

SUBJECT: apex_json.get_varchar2(p_path => 'items[1].rows['|| i || '][2]')

Change the get_datatype accordingly. For example: get_varchar2, get_number, get_date etc. See APEX_JSON function for syntax. It may be easier to return the JSON as varchar2 and convert it in a separate procedure to avoid datatype errors.

Revisit the JSONPath Expression's created in 'Step Four - Formulate the JSON Path Expression' and map them to APEX_JSON.

Convert the JSONPath to APEX_JSON. Taking into consideration the LOOP / Count 'i' variable.

Repeat the process of adding one position to the starting value. i.e. [0] -> [1], [1] -> [2].

ID:

items[0]["rows"][0][0]         ---------------->

items[0]["rows"][1][0]         ---------------->      apex_json.get_varchar2(p_path => 'items[1].rows['|||| '][1]')

items[0]["rows"][2][0]         ---------------->

SUBJECT:

items[0]["rows"][0][1]         ---------------->

items[0]["rows"][1][1]         ---------------->      apex_json.get_varchar2(p_path => 'items[1].rows['|||| '][2]')

items[0]["rows"][2][1]         ---------------->

Additional Code Advice (p_path):

Keep in mind that p_path is expecting a string.

Therefore, it is necessary to concatenate any dynamic variables such as the LOOP / Count 'i' variable.

 

 

DECLARE
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := 'https://yoursite.rightnowdemo.com/services/rest/connect/v1.3/queryResults/?query=select%20id,%20subject%20from%20incidents%20where%20id%20<12500';
--
apex_web_service.g_request_headers(1).name := 'Accept'; apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8'; apex_web_service.g_request_headers(2).name := 'Content-Type';

apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_username => 'username',
p_password => 'password',
p_http_method => 'GET'
);
apex_json.parse(l_ws_response_clob);
--sys.dbms_output.put_line('found '||apex_json.get_varchar2(p_path => 'items[1].rows[3][2]'));
--sys.dbms_output.put_line('Number of Incidents: '||apex_json.get_varchar2(p_path => 'items[1].count'));
for i in 1..apex_json.get_number(p_path => 'items[1].count') LOOP
INSERT INTO RIGHT_NOW_REST(ID, SUBJECT)
VALUES
(
apex_json.get_varchar2(p_path => 'items[1].rows['|| i || '][1]'),
apex_json.get_varchar2(p_path => 'items[1].rows['|| i || '][2]')
);
--sys.dbms_output.put_line('Col1:'|| apex_json.get_varchar2(p_path => l_col1_path) || ' Col2:' || apex_json.get_varchar2(p_path => l_col2_path)) ;
end loop;
--

 

 

Step Seven - Execute the PL/SQL

 

Run the PL/SQL in Apex SQL Commands.

"1 row(s) inserted." should appear in the results.

This PL/SQL code snippet could also be incorporated through many other mechanisms such as stored procedure, web service etc. Since these topics have been covered in past blogs this will not be discussed here.

 

 

Step Eight - Review the Results

 

Confirm data was inserted as expected.

select * from RIGHT_NOW_REST;

Snap9

 

 

 

Addendum

Eloqua should work with similar code.  This is an example code snippet for that.

DECLARE
l_ws_response_clob CLOB;
l_ws_url           VARCHAR2(500) := 'https://secure.server.com/API/REST/1.0/data/contacts?search=%27C_DateModified%3E04/19/2016%27&depth=partial';
idnum number;
fName varchar2(100);
lname varchar2(100);
emailaddr varchar2(200);
--
apex_web_service.g_request_headers(1).name := 'Accept'; apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8'; apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Basic authkey=';
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
  p_username => 'schema\user',
     p_password => 'password',
p_http_method => 'GET'
);
apex_json.parse( p_source=> l_ws_response_clob);
FOR i IN 1 .. apex_json.get_number(p_path => 'total') LOOP
idnum      := apex_json.get_varchar2(p_path => 'elements[' || i || '].id');
fName      := apex_json.get_varchar2(p_path => 'elements[' || i || '].firstName');
lname      := apex_json.get_varchar2(p_path => 'elements[' || i || '].lastName');
emailaddr  := apex_json.get_varchar2(p_path => 'elements[' || i || '].emailAddress');
--dbms_output.put_line('id:' || idnum);
--dbms_output.put_line('fName:' || fName);
--dbms_output.put_line('lname:' || lname);
--dbms_output.put_line('emailaddr:' || emailaddr);
INSERT INTO RESPONSES_CONTACTS_DATA (ID, FIRSTNAME, LASTNAME, EMAILADDRESS)
VALUES
(idnum, fName, lname, emailaddr);
END LOOP;
--

 

Want to Learn More?

 

Click here for more A-Team OACS Blogs

Click here for more A-Team BICS Blogs

Click here for more A-Team OBIEE Blogs

Click here for the Application Express API Reference Guide - MAKE_REST_REQUEST Function.

Click here for the Application Express API Reference Guide - APEX_JSON Package.

Click here for the Oracle Service Cloud Connect REST API Developers Guide - August 2015.

 

Summary

 

This article provided a set of examples that leverage the APEX_WEB_SERVICE_API to integrate Oracle Service Cloud (RightNow) with Oracle Business Intelligence Cloud Service (BICS) using the Connect REST API web services.

The use case shown was for BICS and Oracle Service Cloud (RightNow) integration. However, many of the techniques referenced could be used to integrate Oracle Service Cloud (RightNow) with other Oracle and non-Oracle applications.

Similarly, the Apex MAKE_REST_REQUEST and APEX_JSON examples can be easily modified to integrate BICS or standalone Oracle Apex with any other REST web service that can be accessed via a URL and returns JSON data.

Techniques referenced in this blog may be useful for those building BICS REST ETL connectors and plug-ins.

Key topics covered in this article include: Oracle Business Intelligence Cloud Service (BICS), Oracle Service Cloud (RightNow), ROQL, Connect REST API, Oracle Apex API, APEX_JSON, apex_web_service.make_rest_request, PL/SQL, and Cloud to Cloud integration.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content