I recently had a requirement to consume Content Server webservices from an APEX application. I opted to do this in the database and build a package to call the rest webservice API’s provided by Content Server. The APEX_WEB_SERVICE package worked great for most of the requests that needed to be made (authenticating and receiving a token, getting node id’s and creating folders).
Keep in mind if the webservice you are calling uses SSL, you will need to setup an Oracle Wallet to store the certificates. The root and any intermediate certificates need to be included. In this example, since the package resides in the database the wallet needs to be created on the database servers rather than the application server.
You will need to set the following headers prior to calling APEX_WEB_SERVICE.make_rest_request:
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded;';
The first call you will need to make to Content Server is to authenticate and receive a token. Use the APEX_JSON package to parse the response to get the token. For this call you will need pass the username and password in the p_username and p_password parameters (this will not be required for subsequent calls since a ticket is passed in the header).
l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => '<your Content Server URL>/api/v1/auth',
p_http_method => 'POST',
p_wallet_path => <path to Oracle wallet>,
p_wallet_pwd => <Oracle wallet password>,
p_username => <Content Server username>,
p_password => <Content Server password>,
p_parm_name => APEX_UTIL.string_to_table('username:password'),
p_parm_value => APEX_UTIL.string_to_table(<Content Server username>||':'||<Content Server password>)
);
apex_json.parse(l_clob);
All subsequent calls to the API will need to include an additional header to pass the OTCS ticket obtained in the authentication API call:
apex_web_service.g_request_headers(2).name := 'otcsticket';
apex_web_service.g_request_headers(2).value := <your ticket>;
Depending on the API being called, most calls should follow a format similar to the example below and return a clob that can be parsed with apex_json. This is an example of request to get the node_id of a folder or document:
l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => '<your Content Server URL>/api/v1/nodes/'||<parent node id>||'/nodes',
p_http_method => 'GET',
p_wallet_path => <path to Oracle wallet>,
p_wallet_pwd => <Oracle wallet password>,
p_parm_name => APEX_UTIL.string_to_table('where_name'),
p_parm_value => APEX_UTIL.string_to_table(<node name>)
);
apex_json.parse(l_clob);
However, I could not get APEX_WEB_SERVICE to work when uploading a document as this required a multipart/form-data request to be made. Nick Buytaert at APEXPLAINED has a great solution for handling multipart/form-data requests that helped me solved my dilemma: UTL_HTTP and a multipart/form-data request body — APEXPLAINED
One thing to note is that even though the request body variable is defined as a clob, Oracle will treat it as a varchar2 if you do string concatenations. If you will be passing data greater than 32k, you will need to use dbms_lob to build up your clob. In my particular situation, I had to break the request body up into three sections and write them to the request separately because the actual file payload needed to be sent as blob:
while l_offset < l_request_body_length_pre loop
dbms_lob.read(l_request_body_pre_file, l_amount, l_offset, l_buffer);
utl_http.write_text(l_http_request, l_buffer);
l_offset := l_offset + l_amount;
end loop;
l_offset := 1;
l_buffer := null;
while l_offset < l_request_body_length_blob loop
dbms_lob.read(p_file_blob, l_amount, l_offset, l_buffer);
utl_http.write_raw(l_http_request, l_buffer);
l_offset := l_offset + l_amount;
end loop;
l_offset := 1;
l_buffer := null;
while l_offset < l_request_body_length_post loop
dbms_lob.read(l_request_body_post_file, l_amount, l_offset, l_buffer);
utl_http.write_text(l_http_request, l_buffer);
l_offset := l_offset + l_amount;
end loop;
Planet of the APEX 