Background

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

This article describes how to integrate Oracle Fusion Applications – WebCenter / Universal Content Management (UCM) with PL/SQL.

Note: Oracle has discontinued File-Based Data Export support for CX Sales and B2B Service objects with the 20D Update. If you need help transitioning from File-Based Data Export, refer to the document Transition from File-Based Data Import to Import and Export Management (Document ID 2576983.1) on MOS.

This article was originally written for the File-Based Data Import Tool which used SOAP Web Services which is no longer the recommended method for CX Sales and B2B. This blog has been left available for those using non-CX products.

The article is divided into four steps:

Step One – Export Management

Step Two – WebCenter / UCM

Step Three - Soap Web Services

Step Four - PL/SQL

 Export Management

1.1 - Click Tools -> Export Management

 1.2 - Click -> Create Export Activity

1.3 - Enter Export Options -> Select Object = Sales Territory

1.4 - Add a filter

1.5 - Select fields to export

1.6 - Review and Submit

1.7 - Watch Progress

1.8 - Download the Zip and review the CSV contents

 WebCenter / UCM

*** To View attachments in UCM the Attachments Administrator role must be granted to the Fusion user ***

2.1- Login to WebCenter / UCM.

https://hostname.fs.em2.oraclecloud.com/cs

      2.2 - Search: Expanded Form

Search on: Title, Security Group = Attachments, and Author Starts with {author name} - all are case sensitive.

2.3 - Click the ID to download the Zip -> unZip to view CSV file.

2.4 - Confirm the CSV file contains the expected data-set.

 SOAP Web Services

3.1 - Confirm that the idcws/GenericSoapPort?wsdl is accessible (available from Fusion Applications R10+).

i.e. https://hostname.fs.em2.oraclecloud.com/idcws/GenericSoapPort?wsdl

3.2 Download SoapUI

http://www.soapui.org

Was tested with SoapUI 5.6.0

3.3. - Use GET_SEARCH_RESULTS to get dID.

* Note: [``] tick marks around the UCM ID are required to be in that format. *

(Be mindful of word wrap from copy and paste)

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">
<soapenv:Header>
</soapenv:Header>
<soapenv:Body>
<ucm:GenericRequest webKey="cs">
<ucm:Service IdcService="GET_SEARCH_RESULTS">
<ucm:Document>
<ucm:Field name="QueryText">dDocName &lt;starts>`UCMFA00167550`</ucm:Field>
</ucm:Document>
</ucm:Service>
</ucm:GenericRequest>
</soapenv:Body>
</soapenv:Envelope>

In the response look for the dID

3.4 - Use GET_FILE, passing the dID to get the Zip file.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">
<soapenv:Header>
</soapenv:Header>
<soapenv:Body>
<ucm:GenericRequest webKey="cs">
<ucm:Service IdcService="GET_FILE">
<ucm:Document>
<ucm:Field name="dID">215815</ucm:Field>
</ucm:Document>
</ucm:Service>
</ucm:GenericRequest>
</soapenv:Body>
</soapenv:Envelope>

In Request Properties set Enable MTOM, Force MTOM, and Expand MTOM Attachments = true 

The base64 encoded file is visible in the response.

Click on attachments -> click on the export button to save the zip file locally -> confirm it's contents

 PL/SQL (this section of the blog has not been re-tested on latest Fusion release)

*** This PL/SQL is not working in Fusion 12D and is in the process of being re-test / re-written ***

This section outlines how to build the Soap requests that utilizes the public idcws/GenericSoapPort?wsdl (available in Fusion R10+). “GET_SEARCH_RESULTS” is used to retrieve the “dID” based on the “UCM ID” of the CSV file (gathered from Step Two). “GET_FILE” is then used to retrieve the file associated the given “dID”. The file is returned as a SOAP attachment.

Generally speaking, Make_Rest_Request is reserved for RESTful Web Services and apex_web_service.make_request for Soap Web Services. However, in this case it was not possible to use apex_web_service.make_request as the data returned was not compatible with the mandatory output of XMLTYPE. Apex_web_service.make_rest_request has been used as a workaround as it offers additionally flexibility, allowing the data to be retrieved as a CLOB.

For “GET_SEARCH_RESULTS” the non-XML components of the file are removed, and the data is saved as XMLTYPE so that the namespace can be used to retrieve the “dID”.

For “GET_FILE” the data is kept as a CLOB. The non-CSV components of the file are removed from the CLOB. Then the data is parsed to the database using “csv_util_pkg.clob_to_csv” that is installed from the Alexandria PL/SQL Utility Library.

 Code the Stored Procedure

4.1 - Test GET_SEARCH_RESULTS PL/SQL

(a)    Copy the below PL/SQL code in SQL Developer or SQL query tool of choice.

(b)    Replace:

(i) Username

(ii) Password

(iii) Hostname

(iv) dDocName i.e. UCMFA001069

* SET DEFINE OFF was needed to run in SQLDeveloper *

SET DEFINE OFF;

DECLARE
l_user_name VARCHAR2(100) := 'username';
l_password VARCHAR2(100) := 'password';
l_ws_url VARCHAR2(500) := 'https://hostname.fs.us2.oraclecloud.com/idcws/GenericSoapPort?wsdl';
l_ws_action VARCHAR2(500) := 'urn:GenericSoap/GenericSoapOperation';
l_ws_response_clob CLOB;
l_ws_response_clob_clean CLOB;
l_ws_envelope CLOB;
l_http_status VARCHAR2(100);
v_dID VARCHAR2(100);
l_ws_resp_xml XMLTYPE;
l_start_xml PLS_INTEGER;
l_end_xml PLS_INTEGER;
l_resp_len PLS_INTEGER;
l_xml_len PLS_INTEGER;
clob_l_start_xml PLS_INTEGER;
clob_l_resp_len PLS_INTEGER;
clob_l_xml_len PLS_INTEGER;
clean_clob_l_end_xml PLS_INTEGER;
clean_clob_l_resp_len PLS_INTEGER;
clean_clob_l_xml_len PLS_INTEGER;
v_cdata VARCHAR2(100);
v_length INTEGER;
BEGIN
l_ws_envelope :=
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">
<soapenv:Body>
<ucm:GenericRequest webKey="cs">
<ucm:Service IdcService="GET_SEARCH_RESULTS">
<ucm:Document>
<ucm:Field name="QueryText">dDocName &lt;starts> `UCMFA00167550`</ucm:Field>
</ucm:Document>
</ucm:Service>
</ucm:GenericRequest>
</soapenv:Body>
</soapenv:Envelope>';
apex_web_service.g_request_headers(1).name := 'SOAPAction';
apex_web_service.g_request_headers(1).value := l_ws_action;
apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'text/xml; charset=UTF-8';
l_ws_response_clob := apex_web_service.make_rest_request(
p_url => l_ws_url,
p_http_method => 'POST',
p_body => l_ws_envelope,
p_username => l_user_name,
p_password => l_password);
--dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,24000,1));
--Tested on a very small CLOB. Less than 32767. If larger may need to slice.
--dbms_output.put_line(length(l_ws_response_clob));
--Remove header as it is not XML
clob_l_start_xml := INSTR(l_ws_response_clob,'<?xml',1,1);
clob_l_resp_len := LENGTH(l_ws_response_clob);
clob_l_xml_len := clob_l_resp_len - clob_l_start_xml + 1;
l_ws_response_clob_clean := dbms_lob.substr(l_ws_response_clob,clob_l_xml_len,clob_l_start_xml);
--dbms_output.put_line(l_ws_response_clob_clean);
--Remove the tail as it is not XML
clean_clob_l_end_xml := INSTR(l_ws_response_clob_clean,'------=',1,1);
clean_clob_l_resp_len := LENGTH(l_ws_response_clob_clean);
clean_clob_l_xml_len := clean_clob_l_end_xml - 1;
l_ws_response_clob_clean := dbms_lob.substr(l_ws_response_clob_clean,clean_clob_l_xml_len,1);
--dbms_output.put_line(l_ws_response_clob_clean);
--Convert CLOB to XMLTYPE
l_ws_resp_xml := XMLTYPE.createXML(l_ws_response_clob_clean);
select (cdata_section)
into v_cdata
from
xmltable
(
xmlnamespaces
(
'http://schemas.xmlsoap.org/soap/envelope/' as "env",
'http://www.oracle.com/UCM' as "ns2"
),
'//env:Envelope/env:Body/ns2:GenericResponse/ns2:Service/ns2:Document/ns2:ResultSet/ns2:Row/ns2:Field[@name="dID"]'
passing l_ws_resp_xml
columns
cdata_section VARCHAR2(100) path 'text()'
) dat;
dbms_output.put_line('dID:' || v_cdata);
END;

 (c)    The Results should show the corresponding dID.

4.2 - Install the relevant alexandria-plsql-utils

* Required to run in ADW as ADMIN user: GRANT DEBUG CONNECT SESSION to <DB_USER>; *

(a)    Go to: https://github.com/mortenbra/alexandria-plsql-utils

(b)    Click Download ZIP

(c)    Run these three sql scripts / packages in this order:

\plsql-utils-v170\setup\types.sql

\plsql-utils-v170\ora\csv_util_pkg.pks

\plsql-utils-v170\ora\csv_util_pkg.pkb

4.3 - Create table in ADW to insert data into.

CREATE TABLE OAC_USER.TERRITORY_INFO(
Territory_Name VARCHAR(100),
Territory_Number VARCHAR(100),
Status VARCHAR(100),
Type VARCHAR(100)
);

4.4 - Test UCM_GET_FILE STORED PROCEDURE

(a)    Copy the below PL/SQL code into SQLDeveloper

(b)    Replace:

(I) Column names in SQL INSERT as needed

(Ii) Header name of first column. i.e. "Territory Name"

CREATE OR REPLACE PROCEDURE UCM_GET_FILE
(
p_ws_url VARCHAR2,
p_user_name VARCHAR2,
p_password VARCHAR2,
p_dID VARCHAR2
) IS
l_ws_envelope CLOB;
l_ws_response_clob CLOB;
l_ws_response_clob_clean CLOB;
l_ws_url VARCHAR2(500) := p_ws_url;
l_user_name VARCHAR2(100) := p_user_name;
l_password VARCHAR2(100) := p_password;
l_ws_action VARCHAR2(500) := 'urn:GenericSoap/GenericSoapOperation';
l_ws_resp_xml XMLTYPE;
l_start_xml PLS_INTEGER;
l_end_xml PLS_INTEGER;
l_resp_len PLS_INTEGER;
clob_l_start_xml PLS_INTEGER;
clob_l_resp_len PLS_INTEGER;
clob_l_xml_len PLS_INTEGER;
clean_clob_l_end_xml PLS_INTEGER;
clean_clob_l_resp_len PLS_INTEGER;
clean_clob_l_xml_len PLS_INTEGER;
BEGIN
l_ws_envelope :=
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">
<soapenv:Body>
<ucm:GenericRequest webKey="cs">
<ucm:Service IdcService="GET_FILE">
<ucm:Document>
<ucm:Field name="dID">'|| p_dID ||'</ucm:Field>
</ucm:Document>
</ucm:Service>
</ucm:GenericRequest>
</soapenv:Body>
</soapenv:Envelope>
';
apex_web_service.g_request_headers(1).name := 'SOAPAction';
apex_web_service.g_request_headers(1).value := l_ws_action;
apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'text/xml; charset=UTF-8';
l_ws_response_clob := apex_web_service.make_rest_request(
p_url => l_ws_url,
p_http_method => 'POST',
p_body => l_ws_envelope,
p_username => l_user_name,
p_password => l_password);
--Note: This was tested with a very small result-set
--dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,24000,1));
--Tested on a very small CLOB. Less than 32767. If larger may need to slice.
--dbms_output.put_line(length(l_ws_response_clob));
--Remove junk header
clob_l_start_xml := INSTR(l_ws_response_clob,'"Territory Name"',1,1);
clob_l_resp_len := LENGTH(l_ws_response_clob);
clob_l_xml_len := clob_l_resp_len - clob_l_start_xml + 1;
l_ws_response_clob_clean := dbms_lob.substr(l_ws_response_clob,clob_l_xml_len,clob_l_start_xml);
--dbms_output.put_line(l_ws_response_clob_clean);
--Remove junk footer
clean_clob_l_end_xml := INSTR(l_ws_response_clob_clean,CHR(13),-3)-2;
clean_clob_l_resp_len := LENGTH(l_ws_response_clob_clean);
clean_clob_l_xml_len := clean_clob_l_end_xml;
l_ws_response_clob_clean := dbms_lob.substr(l_ws_response_clob_clean,clean_clob_l_xml_len,1);
-- dbms_output.put_line(l_ws_response_clob_clean);
--Insert into database
DELETE FROM OAC_USER.TERRITORY_INFO;
INSERT INTO OAC_USER.TERRITORY_INFO (Territory_Name,Territory_Number,Status,Type)
select C001,C002,C003,C004 FROM table(csv_util_pkg.clob_to_csv(l_ws_response_clob_clean,',',1));
END;

(c)    Replace:

(i) Username

(ii) Password

(iii) Hostname

(iv) dID i.e. 1011

BEGIN
UCM_GET_FILE('https://hostname.fs.us2.oraclecloud.com/idcws/GenericSoapPort?wsdl','username','password','dID');
END;

(d)    Confirm data was loaded successfully

SELECT * FROM TERRITORY_INFO;

4.5 - Combine GET_SEARCH_RESULTS and GET_FILE

(a)    Copy the below PL/SQL code into SQLDeveloper.

(b)    Replace:

(i) Username

(ii) Password

(iii) Hostname

(iv) dDocName i.e. UCMFA001069

(c)    Note: The code highlighted in green is the only change made to the original.

(d)    Note: This code would also be converted to a stored procedure with parameters should it be implemented in production.

DECLARE
l_user_name VARCHAR2(100) := 'username';
l_password VARCHAR2(100) := 'password';
l_ws_url VARCHAR2(500) := 'https://hostname.fs.us2.oraclecloud.com/idcws/GenericSoapPort?wsdl';
l_ws_action VARCHAR2(500) := 'urn:GenericSoap/GenericSoapOperation';
l_ws_response_clob CLOB;
l_ws_response_clob_clean CLOB;
l_ws_envelope CLOB;
l_http_status VARCHAR2(100);
v_dID VARCHAR2(100);
l_ws_resp_xml XMLTYPE;
l_start_xml PLS_INTEGER;
l_end_xml PLS_INTEGER;
l_resp_len PLS_INTEGER;
l_xml_len PLS_INTEGER;
clob_l_start_xml PLS_INTEGER;
clob_l_resp_len PLS_INTEGER;
clob_l_xml_len PLS_INTEGER;
clean_clob_l_end_xml PLS_INTEGER;
clean_clob_l_resp_len PLS_INTEGER;
clean_clob_l_xml_len PLS_INTEGER;
v_cdata VARCHAR2(100);
v_length INTEGER;
BEGIN
l_ws_envelope :=
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM">
<soapenv:Body>
<ucm:GenericRequest webKey="cs">
<ucm:Service IdcService="GET_SEARCH_RESULTS">
<ucm:Document>
<ucm:Field name="QueryText">dDocName &lt;starts> `UCMFA001069`</ucm:Field>
</ucm:Document>
</ucm:Service>
</ucm:GenericRequest>
</soapenv:Body>
</soapenv:Envelope>';
apex_web_service.g_request_headers(1).name := 'SOAPAction';
apex_web_service.g_request_headers(1).value := l_ws_action;
apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'text/xml; charset=UTF-8';
l_ws_response_clob := apex_web_service.make_rest_request(
p_url => l_ws_url,
p_http_method => 'POST',
p_body => l_ws_envelope,
p_username => l_user_name,
p_password => l_password);
--dbms_output.put_line(dbms_lob.substr(l_ws_response_clob,24000,1));
--Tested on a very small CLOB. Less than 32767. If larger may need to slice.
--dbms_output.put_line(length(l_ws_response_clob));
--Remove header as it is not XML
clob_l_start_xml := INSTR(l_ws_response_clob,'<?xml',1,1);
clob_l_resp_len := LENGTH(l_ws_response_clob);
clob_l_xml_len := clob_l_resp_len - clob_l_start_xml + 1;
l_ws_response_clob_clean := dbms_lob.substr(l_ws_response_clob,clob_l_xml_len,clob_l_start_xml);
--dbms_output.put_line(l_ws_response_clob_clean);
--Remove the tail as it is not XML
clean_clob_l_end_xml := INSTR(l_ws_response_clob_clean,'------=',1,1);
clean_clob_l_resp_len := LENGTH(l_ws_response_clob_clean);
clean_clob_l_xml_len := clean_clob_l_end_xml - 1;
l_ws_response_clob_clean := dbms_lob.substr(l_ws_response_clob_clean,clean_clob_l_xml_len,1);
--dbms_output.put_line(l_ws_response_clob_clean);
--Convert CLOB to XMLTYPE
l_ws_resp_xml := XMLTYPE.createXML(l_ws_response_clob_clean);
select (cdata_section)
into v_cdata
from
xmltable
(
xmlnamespaces
(
'http://schemas.xmlsoap.org/soap/envelope/' as "env",
'http://www.oracle.com/UCM' as "ns2"
),
'//env:Envelope/env:Body/ns2:GenericResponse/ns2:Service/ns2:Document/ns2:ResultSet/ns2:Row/ns2:Field[@name="dID"]'
passing l_ws_resp_xml
columns
cdata_section VARCHAR2(100) path 'text()'
) dat;
--dbms_output.put_line('dID:' || v_cdata);
UCM_GET_FILE(l_ws_url,l_user_name,l_password,v_cdata);
END;

 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 Alexandria-plsql-utils

Click here for REST API for CX Sales and B2B Service guide

Click here for SOAP Web Services for CX Sales and B2B Service guide

 Summary

This article described how to integrate Oracle Fusion Applications – WebCenter / Universal Content Management (UCM) with PL/SQL. It covered the functional and technical steps necessary to automate exporting of data from WebCenter / UCM and load it into a ADW database.

In order to implement this solution the WebCenter / UCM idcws/GenericSoapPort?wsdl must be publicly available.

The SQL scripts provided are for demonstration purposes only. They were tested on small sample data-set. It is anticipated that code adjustments would be need to accommodate larger Production data-sets.