Background

* This blog was last tested on Oracle Service Cloud 20B (Build 266) + ADW 18c + CXDev Toolbox 19.10.4.0 *

This article expands on "Integrating Oracle Service Cloud (RightNow) with PL/SQL – using Connect Web Services for SOAP" first 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.

The first blog described how to integrate Oracle Service Cloud (RightNow) with PL/SQL using Connect Web Services for Simple Object Access Protocol (SOAP).

This blog 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 Oracle Service Cloud. The Connect REST API has been available as of the May 2015 release of Oracle Service Cloud. 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 ADW using PL/SQL ran through SQLDeveloper. The code snippets may then be incorporated into a stored procedure or web service and scheduled / triggered. (Such topics have been covered in past blogs by this author.)

1)    Construct ROQL Query

2)    Test Connect REST API Query URL

3)    Run apex_web_service.make_rest_request

4)    Formulate JSON Path Expression

5)    Create Database Table

6)    Add APEX_JSON parse code to the PL/SQL

7)    Execute PL/SQL

8)    Review Results

 Construct ROQL Query

See Step Two of "Integrating Oracle Service Cloud (RightNow) with PL/SQL – using Connect Web Services for SOAP" on how to construct the ROQL Query. For this example the ROQL Query is:

select id, subject from incidents where id <12500

 Test 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>

2.1)    Take the ROQL Query from Step One and replace all the spaces with %20. Replace < with %3c.

For a full list of special character conversions see Query String wiki.

For example: select%20id,%20subject%20from%20incidents%20where%20id%20%3c12500

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

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

2.3)    Test the GET request in Postman - using Basic Authentication.

Don't forget to add the "osvc-crest-application-context" custom header.

 Run apex_web_service.make_rest_request

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

Run the final PL/SQL in SQLDeveloper or SQL query tool of choice.

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

DECLARE
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := 'https://<your_site_interface>/services/rest/connect/v1.4/queryResults/?query=select%20id,%20subject%20from%20incidents%20where%20id%20%3c12500';
--
apex_web_service.g_request_headers(1).name := 'Accept';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'application/json';
apex_web_service.g_request_headers(3).name := 'OSvC-CREST-Application-Context';
apex_web_service.g_request_headers(3).value := 'Retrieve Data';
l_ws_response_clob := apex_web_service.make_rest_request
(
p_url => l_ws_url,
p_username => 'user',
p_password => 'pwd',
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));
--;

3.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.

 Formulate JSON Path Expression

4.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.

4.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?"
]

 Create Database Table

5.1)    Create the RIGHT_NOW_REST table in SQLDeveloper or SQL query tool of choice.

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

 Add APEX_JSON parse code to PL/SQL

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:

6.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].

6.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['|| i || '][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['|| i || '][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://<your_site_interface>/services/rest/connect/v1.4/queryResults/?query=select%20id,%20subject%20from%20incidents%20where%20id%20%3c12500';
--
apex_web_service.g_request_headers(1).name := 'Accept';

apex_web_service.g_request_headers(1).value := 'application/json';

apex_web_service.g_request_headers(2).name := 'Content-Type';

apex_web_service.g_request_headers(2).value := 'application/json';

apex_web_service.g_request_headers(3).name := 'OSvC-CREST-Application-Context';

apex_web_service.g_request_headers(3).value := 'Retrieve Data';

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 OAC_USER.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;
--

 Execute PL/SQL

Run the PL/SQL in SQLDeveloper or SQL query tool of choice.

Expected Result: PL/SQL procedure successfully completed.

 Review Results

Confirm data was inserted as expected.

select * from OAC_USER.RIGHT_NOW_REST;

 Addendum

* Note this section has not been tested in the most recent Eloqua release *

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 OAC 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 REST API Guide for Oracle B2C Service

 Summary

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

The use case shown was for PL/SQL 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 other REST web service that can be accessed via a URL and returns JSON data.

Key topics covered in this article include: 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.