1 / 31

Data Integration: Oracle APEX and WebCenter Content Integration Solutions

Data Integration: Oracle APEX and WebCenter Content Integration Solutions . Topics. Introduction WSDL Locations ApEx integration PL/SQL Java. Getting Started – Prepare Environment. WSDL Location <domain>/ucm/cs/weblayout/groups/secure/wsdl/custom WSDL URL http://<host>/_dav/cs/idcplg

kamuzu
Télécharger la présentation

Data Integration: Oracle APEX and WebCenter Content Integration Solutions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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>';

  12. ApEx Page Process

  13. 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

  14. 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

  15. 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

  16. 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>

  17. 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.

  18. 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

  19. 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);

  20. Partial UCM Data Model

  21. 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

  22. Java Solution

  23. 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); }...

  24. 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(); }

  25. 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>

  26. 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

  27. 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

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

More Related