Calling a SharePoint web service from PL/SQL

This post is a quick outline on how to consume a Microsoft SharePoint web service from within the Oracle Database. The SharePoint instance I was dealing with had NTLM A enabled. The support for this type of authentication in PL/SQL is pretty much non-existent. The magnificent Alexandria PL/SQL Utilities library, however, includes the NTLM_HTTP_PKG package which does all the heavy lifting for you in this area. I simply installed the Alexandria library in a separate database schema and granted execute privileges to my APEX application’s parsing schema. A great example on how open source initiatives can make a big difference.

The next step for me was to actually call a SharePoint web service. I started off by trying to upload a (BLOB) file to a document library. For this functionality, I have used the Copy endpoint and CopyIntoItems SOAP action. An overview of all available actions under a specific endpoint is available by browsing to the following kind of URLs:

http://<sharepoint-server>/<customsite>/_vti_bin/Copy.asmx
http://<sharepoint-server>/<customsite>/_vti_bin/Dws.asmx
http://<sharepoint-server>/<customsite>/_vti_bin/Lists.asmx
...

Also, don’t forget to properly configure your database’s Access Control List (ACL) before invoking the web service. You’ll run into “ORA-24247: network access denied by access control list (ACL)” when something is missing in your ACL.

My plan was to use the APEX_WEB_SERVICE API package to invoke the SharePoint web service. Unfortunately, this technique didn’t work out for me. Using the APEX_WEB_SERVICE package, I constantly received a 401 Unauthorized response, telling me that there was something wrong with the user authentication. Now, I can’t tell you what the problem was, but calling the web service in SoapUI did give back a successful result. That’s why I stopped using the APEX_WEB_SERVICE package and started using UTL_HTTP instead, with success this time.

Here’s the code that calls the Copy SharePoint web service and uploads a file to a target document library.

set serveroutput on;
declare
  l_file_location varchar2(400) := 'http://my.sharepoint.com/org/department/MY_DOC_LIBRARY/';
  l_filename varchar2(256) := 'my-new-file.docx';
  l_file_content blob;

  lco_ntlm_auth_domain constant varchar2(30) := 'MY.DOMAIN';
  lco_ntlm_auth_username constant varchar2(30) := 'MY_USERNAME';
  lco_ntlm_auth_password constant varchar2(30) := 'MY_PASSWORD';
  l_ntlm_auth_string varchar2(4000);

  lco_web_service_url constant varchar2(400) := 'http://my.sharepoint.com/org/department/_vti_bin/Copy.asmx';
  lco_web_service_soap_action constant varchar2(400) := '"http://schemas.microsoft.com/sharepoint/soap/CopyIntoItems"';

  l_http_request utl_http.req;
  l_request_body clob;
  l_request_body_length number;

  l_http_response utl_http.resp;
  l_response_header_name varchar2(256);
  l_response_header_value varchar2(1024);
  l_response_body varchar2(32767);

  l_offset number := 1;
  l_amount number := 2000;
  l_buffer varchar2(2000);
begin
  l_ntlm_auth_string := alexandria.ntlm_http_pkg.begin_request(
                          p_url => lco_web_service_url,
                          p_username => lco_ntlm_auth_domain || '\' || lco_ntlm_auth_username,
                          p_password => lco_ntlm_auth_password
                        );

  select my_blob
  into l_file_content
  from my_file;

  l_request_body :=
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
   <CopyIntoItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
     <SourceUrl>' || l_filename || '</SourceUrl>
     <DestinationUrls>
       <string>' || l_file_location || l_filename || '</string>
     </DestinationUrls>
     <Fields>
       <FieldInformation Type="File" DisplayName="' || l_filename || '" Value="' || l_filename || '" />
     </Fields>
     <Stream>' || apex_web_service.blob2clobbase64(l_file_content) || '</Stream>
   </CopyIntoItems>
 </soap:Body>
</soap:Envelope>';

  l_request_body_length := dbms_lob.getlength(l_request_body);

  l_http_request := utl_http.begin_request(
                      url => lco_web_service_url,
                      method => 'POST',
                      http_version => 'HTTP/1.1'
                    );
  utl_http.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
  utl_http.set_header(l_http_request, 'Content-Type', 'text/xml;charset=UTF-8');
  utl_http.set_header(l_http_request, 'SOAPAction', lco_web_service_soap_action);
  utl_http.set_header(l_http_request, 'Content-Length', l_request_body_length);
  utl_http.set_header(l_http_request, 'Transfer-Encoding', 'chunked');
  utl_http.set_header(l_http_request, 'Authorization', l_ntlm_auth_string);

  while l_offset < l_request_body_length loop
    dbms_lob.read(l_request_body, l_amount, l_offset, l_buffer);
    utl_http.write_text(l_http_request, l_buffer);
    l_offset := l_offset + l_amount;
  end loop;

  l_http_response := utl_http.get_response(l_http_request);
  dbms_output.put_line('Response> Status Code: ' || l_http_response.status_code);
  dbms_output.put_line('Response> Reason Phrase: ' || l_http_response.reason_phrase);
  dbms_output.put_line('Response> HTTP Version: ' || l_http_response.http_version);

  for i in 1 .. utl_http.get_header_count(l_http_response) loop
    utl_http.get_header(l_http_response, i, l_response_header_name, l_response_header_value);
    dbms_output.put_line('Response> ' || l_response_header_name || ': ' || l_response_header_value);
  end loop;

  utl_http.read_text(l_http_response, l_response_body, 32767);
  dbms_output.put_line(l_response_body);

  if l_http_request.private_hndl is not null then
    utl_http.end_request(l_http_request);
  end if;

  if l_http_response.private_hndl is not null then
    utl_http.end_response(l_http_response);
  end if;
exception
  when others then
    if l_http_request.private_hndl is not null then
      utl_http.end_request(l_http_request);
    end if;

    if l_http_response.private_hndl is not null then
      utl_http.end_response(l_http_response);
    end if;

    raise;
end;
/

Executing the above code gives me back the following output.

PL/SQL procedure successfully completed.

Response> Status Code: 200
Response> Reason Phrase: OK
Response> HTTP Version: HTTP/1.1
Response> Cache-Control: private, max-age=0
Response> Content-Type: text/xml; charset=utf-8
Response> Server: Microsoft-IIS/7.0
Response> SPRequestGuid: 890faafe-cfa4-43b1-8a74-ec2a4196b0d2
Response> X-SharePointHealthScore: 2
Response> X-AspNet-Version: 2.0.50727
Response> X-Powered-By: ASP.NET
Response> MicrosoftSharePointTeamServices: 14.0.0.7116
Response> X-MS-InvokeApp: 1; RequireReadOnly
Response> Date: Thu, 05 Nov 2015 08:15:18 GMT
Response> Content-Length: 538

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soap:Body>
    <CopyIntoItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <CopyIntoItemsResult>0</CopyIntoItemsResult>
      <Results>
        <CopyResult ErrorCode="Success" DestinationUrl="http://my.sharepoint.com/org/department/MY_DOC_LIBRARY/my-new-file.docx" />
      </Results>
    </CopyIntoItemsResponse>
  </soap:Body>
</soap:Envelope>

These are the products that I have used when writing the above code:

• Oracle Database 11g Express Edition 11.2.0.2.0 with Oracle Application Express 4.0.2
• Microsoft SharePoint 2010 without SSL, with NTLM authentication