Data Integration: Oracle APEX and WebCenter Content Integration Solutions

1. Data Integration: Oracle APEX and WebCenter Content Integration Solutions

2. Topics • Introduction • WSDL Locations • ApEx integration • PL/SQL • Java

3. Getting Started – Prepare Environment • WSDL Location • <domain>/ucm/cs/weblayout/groups/secure/wsdl/custom • WSDL URL • http://<host>/_dav/cs/idcplg • Action • http://www.stellent.com/CheckIn

4. Getting Started – WSDL • The ApEx application will use UTL_HTTP to make the SOAP call. • Use Basic Authentication • Username and password • Edit WSDL to allow for basic Authentication • Copy WSDL to another location • Prevents edits from being overwritten upon UCM restart • Applies to versions pre Patch Set 3

5. WSDL Changes This edit allows for Basic Authentication Change This line at the end of the file To This

6. Alternatives Create a Java interface to call WSDL Create a PL/SQL procedure ApEx Process

7. Basic Functionality Content will be checked into Contribution Folders Content will have No Security Content will not be filed in specific folder Checkin will be performed by User specified in code or preferably database table

8. Additional Functionality Passing metadata Filing the content in specific UCM folders Setting Security Group Setting Document Account

9. Requirements • SOAP envelope must be Character Large Object • ApEx stores file as BLOB, so must convert file prior to building envelope • User in the request must have permission to check-in files • Must pass in a dDocName (Content ID) • Must pass the primary file • UTL_HTTP package installed in DB • UCM URL must be

10. Process Flow

11. Build SOAP Envelope l_CLOB := flex_ws_api.blob2clobbase64(l_BLOB); htp.print('this is a test ' || l_filename); l_envelope := q'!<?xml version='1.0' encoding='UTF-8'?>!'; l_envelope := l_envelope || '<soapenv:Envelopexmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:chec="http://www.stellent.com/CheckIn/"> <soapenv:Header/> <soapenv:Body> <chec:CheckInUniversal> <chec:dDocName>'||:P71_CR_MAS_VERIGY_NO||'_'|| :P71_DOC_TYPE ||'_'||:P71_DOC_ID_TEMP ||'</chec:dDocName> <chec:dDocTitle>'||l_filename||'</chec:dDocTitle> <chec:dDocType>Document</chec:dDocType> <chec:dDocAuthor>GM</chec:dDocAuthor> <chec:dSecurityGroup>Public</chec:dSecurityGroup> <chec:dDocAccount></chec:dDocAccount> <chec:CustomDocMetaData> <chec:property> <chec:name></chec:name> <chec:value></chec:value> </chec:property> </chec:CustomDocMetaData> <chec:primaryFile> <chec:fileName>'||l_filename||'</chec:fileName> <chec:fileContent>'||l_CLOB||'</chec:fileContent> </chec:primaryFile> <chec:alternateFile> <chec:fileName></chec:fileName> <chec:fileContent></chec:fileContent> </chec:alternateFile> <chec:extraProps> <chec:property> <chec:name>dCollectionID</chec:name> <chec:value>'||to_char(l_folder_id)||'</chec:value> </chec:property> </chec:extraProps> </chec:CheckInUniversal> </soapenv:Body> </soapenv:Envelope>';

13. ApEx Page Process

14. SOAP Envelope Header l_CLOB := flex_ws_api.blob2clobbase64(l_BLOB); htp.print('this is a test ' || l_filename); l_envelope := q'!<?xml version='1.0' encoding='UTF-8'?>!'; l_envelope := l_envelope || '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:chec="http://www.stellent.com/CheckIn/"> <soapenv:Header/> • Flex_ws_api is an api available on -http://jastraub.blogspot.com/2009/11/flexwsapi-no-on-samplecodeoraclecom.html • Contains blob2clobbase64 function • Apex reads files as BLOB • Envelope requirement – CLOB • l_envelope := q'!<?xml version='1.0' encoding='UTF-8'?>!'; • q is used as the quote delimiter • This is required to build the CLOB envelope

15. SOAP Body <soapenv:Body> <chec:CheckInUniversal> <chec:dDocName>'||:P71_CR_MAS_VERIGY_NO||'_'|| :P71_DOC_TYPE ||'_'||:P71_DOC_ID_TEMP ||'</chec:dDocName> <chec:dDocTitle>'||l_filename||'</chec:dDocTitle> <chec:dDocType>Document</chec:dDocType> <chec:dDocAuthor>GM</chec:dDocAuthor> <chec:dSecurityGroup>Public</chec:dSecurityGroup> <chec:dDocAccount></chec:dDocAccount> • CheckInUniversal • Name of command in WSDL • Required Fields

16. SOAP Custom Metadata <chec:CustomDocMetaData> <chec:property> <chec:name></chec:name> <chec:value></chec:value> </chec:property> </chec:CustomDocMetaData> Used to pass additional metadata fields Name / Value Pair

17. SOAP File <chec:primaryFile> <chec:fileName>'||l_filename||'</chec:fileName> <chec:fileContent>'||l_CLOB||'</chec:fileContent> </chec:primaryFile> <chec:alternateFile> <chec:fileName></chec:fileName> <chec:fileContent></chec:fileContent> </chec:alternateFile>

18. SOAP UCM Folder Location <chec:extraProps> <chec:property> <chec:name>dCollectionID</chec:name> <chec:value>'||to_char(l_folder_id)||'</chec:value> </chec:property> </chec:extraProps> </chec:CheckInUniversal> </soapenv:Body> </soapenv:Envelope>'; dCollectionID is the unique identifier from the Collections Table within the WebCenter Content DB Schema Query to be provided.

19. Submit SOAP Request carma_apex.flex_ws_api.make_request( p_url => 'http://<CONTENT HOST:PORT/_dav/cs/idcplg', p_action => 'http://www.stellent.com/CheckIn/', p_collection_name => 'STELLENT_CHECKIN', p_envelope => l_envelope, p_username => ’<USER_NAME>', p_password => ’<PASSWORD>' ); May desire to store the Content ID used to associate the APEX record with a specific UCM Content ID

20. Content DB Schema Get CollectionID select dcollectionid into l_folder_id from ( select dcollectionid, dcollectionname from dev_ocs.collections where substr(replace(dcollectionname,' ',''),1,4) = substr(:P71_CR_MAS_VERIGY_NO,1,4) and length(dcollectionname) > 4 ) where substr(replace(dcollectionname,' ',''),1,4) = substr(:P71_CR_MAS_VERIGY_NO,1,4) and (substr(replace(dcollectionname,' ',''),length(replace(dcollectionname,' ',''))-4,2) = l_cr_week or substr(replace(dcollectionname,' ',''),length(replace(dcollectionname,' ',''))-1,2) = l_cr_week);

21. Partial UCM Data Model

22. How it works File Browser is the ApEx built in browser File is passed to Flex_WS_API to convert to CLOBBase64 Build the SOAP Envelope using all required and desired optional fields Envelope is sent using UTL_HTTP

24. Java Solution

26. GetCMS.java: Get File from CMS protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String docName = request.getParameter("docName"); DbHelper bean = null; ServletContext context = null; context = getServletContext(); String user = context.getInitParameter("user"); String password = context.getInitParameter("password"); CmsGetFilecms = CmsFactory.getInstance().getCmsGetFile(); CmsFile file; try { bean = new DbHelper(); if (docName.equals("") || docName == null){ thrownewServletException("docNameis null orempty"); } file = cms.getDocument(user, password, docName); //bean.saveFileByDocName(file, docName); }...

27. GetCMS.java cont.. Stream file to user BufferedOutputStreamos = null; try { String contentType = CmsUtils.getInstance().getContentType(file.getFileName()); byte[] bytes = file.getFileContent(); os = new BufferedOutputStream(response.getOutputStream()); response.setContentType(contentType); response.setContentLength(bytes.length); response.addHeader("Content-disposition", "inline; filename="+file.getFileName()); if (logger.isDebugEnabled()) { logger.debug(file.getFileName()); } os.write(bytes); os.flush(); }

28. Build SOAP Message for GetFile url = new java.net.URL(locator.getGetFileSoapAddress());soapStub = (GetFileSoapStub)locator.getGetFileSoap(url); result = soapStub.getFileByName(docName, REVISION_SELECTION_LATEST, DEFAULT_RENDENTION, null); SOAPStub Generated from WSDL on the UCM server using ANT <axis-wsdl2java output="${generated.dir}" testcase="true" verbose="true" url="${local.wsdl}" > <mapping namespace="http://axis.apache.org/ns/interop" package="interop" /> </axis-wsdl2java>

29. PL/SQL Solution • Pros • Easy to set up • Runs inside the DB • Accessible to ApEx • Cons • Not as reusable outside of the DB • Some issues retrieving large files

30. Java Solution • Pros • Deploy as a web service to be leveraged by other applications • Not dependent on the DB or ApEx • Cons • Slightly more complex

31. Points to Consider Query to get Collection ID for folder information Security Passing Metadata