* This blog was last tested on Fusion 20d (11.13.20.10.0) + ADW 18c *

 Background

This article describes how to integrate Oracle Sales Cloud (OCS) with Oracle Autonomous Data Warehouse (ADW) using PL/SQL.

Oracle Documentation: 20d REST API for CX Sales and B2B Service REST API for Oracle Sales Cloud

The blog contains a PL/SQL snippet, that utilizes UTL_HTTP commands to call the "Contacts REST Endpoint" to create a contact from ADW.

Alternative Oracle Sales Cloud integration patterns are discussed in these previously published articles:

Integrating Oracle Transaction Business Intelligence (OTBI) Soap API

Integrating Oracle Sales Cloud with Oracle Analytics Cloud (OAC)

The primary difference between the past and current articles is:

The prior two articles focused on using the APEX_WEB_SERVICE.

This current article uses UTL_HTTP and has no dependency on Apex.

That said, ADW does come with Apex and the previously published solutions above will work with ADW. However, some PL/SQL developers may prefer to use native PL/SQL commands.

The techniques referenced in this blog can be easily altered to integrate with other components of the Sales Cloud REST API. Additionally, they may be useful for those wanting to integrate ADW with other Oracle and non-Oracle products using PL/SQL.

There are three steps to this solution:

1. Test Contacts REST Endpoint from Postman

2. Create Convert Blob to Clob Function

3. Run/Test PL/SQL Sample Snippet

"Section 4. Appendix - Import OSC Certificate into Wallet" has been left in the blog for now and may be removed at a later date. This was content from the original DbaaS blog and no longer needed for ADW. Information in this section has not been tested on the latest software and may be stale.

 Test Contacts REST Endpoint from Postman

Confirm that a contact can be created from Postman:

 Create Convert Blob to Clob Function

The snippet uses this v_blobtoclob function created by Burleson Consulting to convert the response blob to a clob.

There are many other online code samples using various methods to convert blobs to clobs that should work just fine as well.

This function isn't actually required to create the contact.

It is however necessary to read the response - since the response comes back as a blob.

 Run PL/SQL Sample Snippet

     Grant the connect and resolve privileges for the fusion host to the user running the PL/SQL i.e. ADMIN

exec dbms_network_acl_admin.create_acl (acl => 'utl_http.xml',description => 'IDCS HTTP ACL',principal => 'ADMIN', is_grant => TRUE,privilege => 'connect',start_date => null,end_date => null);
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'utl_http.xml',principal => 'ADMIN',is_grant => true,privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl (acl => 'utl_http.xml',host => 'fusion-test.fa.xx.oraclecloud.com',lower_port => null,upper_port => null);
commit;

Where necessary, replace the highlighted items:

(a) Wallet Path (was not needed for ADW)

(b) Wallet Password (was not needed for ADW)

(c) Request URL

(d) User

(e) Pwd

(f) Blob to Clob Function Name

Run the code snippet in SQL Developer or SQL*Plus:

DECLARE
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_response_text VARCHAR2(32766);
l_response_raw RAW(32766);
l_inflated_resp blob;
l_body VARCHAR2(30000);
l_clob CLOB;
l_list  pljson_list;
l_party_number VARCHAR2(100);
--
l_body := '{"FirstName": "Jay","LastName": "Pearson","Address": [{"Address1": "100 Oracle Parkway","City": "Redwood Shores","Country": "US","State": "CA"}]}';
UTL_HTTP.set_wallet('');
l_http_request := UTL_HTTP.begin_request ('https://fusion.oraclecloud.com/crmRestApi/resources/11.13.18.05/contacts','POST','HTTP/1.1');
UTL_HTTP.set_authentication(l_http_request,'user','pwd');
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/vnd.oracle.adf.resourceitem+json');
UTL_HTTP.set_header (l_http_request, 'Transfer-Encoding', 'chunked' );
UTL_HTTP.set_header(l_http_request, 'Cache-Control', 'no-cache');
utl_http.write_text(l_http_request, l_body);
l_http_response := UTL_HTTP.get_response(l_http_request);
dbms_output.put_line ('status code: ' || l_http_response.status_code);
dbms_output.put_line ('reason phrase: ' || l_http_response.reason_phrase);
UTL_HTTP.read_raw(l_http_response, l_response_raw,32766);
l_clob := OAC_USER.v_blobtoclob(l_response_raw);
--dbms_output.put_line(dbms_lob.substr(l_clob,24000,1));
l_party_number := substr(l_clob,instr(l_clob,'"PartyNumber"'),instr(l_clob,'"',instr(l_clob,'"PartyNumber"'),4)-instr(l_clob,'"PartyNumber"')+1);
dbms_output.put_line ('Created: ' || l_party_number);
UTL_HTTP.end_response(l_http_response);
--;
/
sho err

Use the PartyNumber in Postman confirm the contact was created and exists in OSC.

https://fusion.oraclecloud.com/crmRestApi/resources/11.13.18.05/contacts/80382387

 Appendix - From original DbaaS blog (this section may contain stale information)

Create Oracle Wallet

ADW is pre-configured with the Oracle Wallet and 70+ common root CA SSL certificates. It is completely transparent to developers when building declarative web services in APEX or when using APEX_WEB_SERVICE API.

DbaaS, on the other hand, does not come pre-configured with the Oracle Wallet. Therefore, a wallet must be created and the OSC certificate imported into the Wallet.

Using PuTTY (or another SSH and Telnet client) log on to the DbaaS instance as oracle or opc.

If set, enter the passphrase.

If logged on as opc, run:

sudo su - oracle

Set any necessary environment variables:

. oraenv

Create the Wallet

orapki wallet create -wallet . -pwd Welcome1

 Import OSC Certificate into Wallet

From a browser (these examples use Chrome) go to the crmCommonApi contacts URL. The screen will be blank.

https://abc1-cloud1234-crm.oracledemos.com/crmCommonApi/resources/11.1.10/contacts

R10 may require the latest/contacts URL:

https://abc1-cloud1234-crm.oracledemos.com/crmCommonApi/resources/latest/contacts

Click on the lock

Click Connection -> Certificate Information

Click "Certification Path". Select "GeoTrust SSL CA - G3".

Click Details -> Copy to File

Click Next

Select "Base-64 encoded X.509 (.CER)

Save locally as oracledemos.cer

Click Finish

Copy the oracledemos.cer file from the PC to the Cloud server. This can be done using SFTP.

Alternatively, follow the steps below to manually create the oracledemos.cer using vi editor and cut and paste between the environments.

Return to PuTTY.

Using the vi editor create the certificate file.

vi oracledemos.cer

Open the locally saved certificate file in NotePad (or other text editor). Select all and copy.

Return to the vi Editor and paste the contents of the certificate into the oracledemos.cer file.

Hit "i" to insert
"Right Click" to paste
Hit "Esc"
Type "wq" to save
Type "ls -l" to confirm oracledemos.cer file was successfully created

Run the following command to add the certificate to the wallet.

orapki wallet add -wallet . -trusted_cert -cert /home/oracle/oracledemos.cer -pwd Welcome1

 Confirm the certificate was successfully added to the wallet.

orapki wallet display -wallet . -pwd Welcome1

 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 REST API for Oracle Sales Cloud guide.

Click here for Oracle Database UTL_HTTP commands.

 Summary

This article provided a code snippet of how to use UTL_HTTP PL/SQL commands in ADW to create a new contact using the REST API for Oracle Sales Cloud.

The techniques referenced in this blog can be easily altered to integrate with other components of the OSC API. Additionally, they may be useful for those wanting to integrate ADW with other Oracle and non-Oracle products using PL/SQL.