Calling UCM built-in Web Service using PLSQL (from E-Biz)

In some cases, we might need to upload Invoice documents or Sales Order Acknowledgements generated in E-biz system to UCM Storage Server. In such cases, here is the PLSQL function to call UCM WebService from Oracle Database, returns status of document upload.


CREATE OR REPLACE FUNCTION checkInUCM (
DocName       IN VARCHAR2,
DocTitle      IN VARCHAR2,
DocType       IN VARCHAR2,
DocAuthor     IN VARCHAR2,
SecurityGroup IN VARCHAR2,
DocAccount       IN VARCHAR2,
Path          IN VARCHAR2)                                                                                         
 return VARCHAR2
AS
   -- SOAP REQUESTS/RESPONSE
   soap_req_msg    VARCHAR2 (2000);
   soap_resp_msg   VARCHAR2 (2000);
   l_docname VARCHAR2(200) := DocName ;
   l_doctitle VARCHAR2(200) := DocTitle;
   --l_doctype  VARCHAR2(200):= DocType;
   l_doctype  VARCHAR2(200):= 'Document';
   --l_docauthor VARCHAR2(200) := DocAuthor;
   l_docauthor VARCHAR2(200) := 'weblogic';
   --l_secgroup VARCHAR2(200):= SecurityGroup;
   l_secgroup VARCHAR2(200):= 'Public';
   l_docaccount  VARCHAR2(200):= DocAccount;
   l_path VARCHAR2(200):= Path;
   l_content_url VARCHAR2(200):= 'http://localhost:16200/_dav/cs/idcplg';
  l_user VARCHAR2(10):='weblogic';
   l_pwd VARCHAR2(10):='welcome1';
   l_status VARCHAR2(1000):=null;

   -- HTTP REQUEST/RESPONSE
   http_req        UTL_HTTP.req;
   http_resp       UTL_HTTP.resp;
BEGIN
   --
   -- Create SOAP request via HTTP
   --
   soap_req_msg :=
      '<?xml version="1.0" encoding="utf-8"?>
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:chec="http://www.stellent.com/CheckIn/">
    <soapenv:Header/>
    <soapenv:Body>
     <chec:CheckInUniversal>
        <chec:dDocName>'||l_docname||'</chec:dDocName>
        <chec:dDocTitle>'||l_doctitle||'</chec:dDocTitle>
        <chec:dDocType>'||l_doctype||'</chec:dDocType>
        <chec:dDocAuthor>'||l_docauthor||'</chec:dDocAuthor>
        <chec:dSecurityGroup>'||l_secgroup||'</chec:dSecurityGroup>
        <chec:dDocAccount>'||l_docaccount||'</chec:dDocAccount>
        <chec:CustomDocMetaData>
           <chec:property>
              <chec:name></chec:name>
              <chec:value></chec:value>
           </chec:property>
        </chec:CustomDocMetaData>
        <chec:primaryFile>
           <chec:fileName>'||l_path||'</chec:fileName>
        </chec:primaryFile>
        <chec:alternateFile>
           <chec:fileName></chec:fileName>
        </chec:alternateFile>
        <chec:extraProps>
           <chec:property>
              <chec:name></chec:name>
              <chec:value></chec:value>
           </chec:property>
        </chec:extraProps>
     </chec:CheckInUniversal>
  </soapenv:Body>
</soapenv:Envelope>';
--
   http_req := UTL_HTTP.begin_request(l_content_url,'POST','HTTP/1.1');
   UTL_HTTP.set_authentication(http_req, l_user, l_pwd);
   UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
   UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (soap_req_msg));
   UTL_HTTP.set_header (http_req, 'SOAPAction', '');
   UTL_HTTP.write_text (http_req, soap_req_msg);
   --
   -- Invoke Request and get Response.
   --
   http_resp := UTL_HTTP.get_response (http_req);
   UTL_HTTP.read_text (http_resp, soap_resp_msg);
   UTL_HTTP.end_response (http_resp);
--  
   SELECT EXTRACTVALUE(XMLTYPE(soap_resp_msg), '//idc:statusMessage', 'xmlns:idc="http://www.stellent.com/CheckIn/"')
   INTO l_status
   FROM dual;  
--
   DBMS_OUTPUT.put_line ('Output: ' || soap_resp_msg);
   return l_status;
END;


Executing Function:
Select checkInUCM('SampleFile','SampleFile',null,null,null,null,'/u01/app/Sample.pdf') from dual;