How to do a custom export from oracle webcenter content (ucm)?

0
votes

I am working with oracle webcenter content (WCC) aka UCM, and i need to do a custom export, similar to single revision replication. I can't use single revision replication since it is bugged on my UCM server version (11.1.1.8.0) and the destiny server may already have multiple revisions of that file. So i've tried to replicate the query generated by the preview option on the archiver applet when i select single revision replication. For each unique content id i need to export only the most recent revision that matches the query, so i've tried the following:

Revisions.dID IN (select max (REV22.dID) from Revisions REV22, DocMeta,
Documents where REV22.dID = Documents.dID and REV22.dID = DocMeta.dID and
Documents.dlsPrimary <> 0 AND REV22.dReleaseState <> 'E' and
REV22.dReleaseState <> 'N' and  (

xCollectionID <> 0 and (
Revisions.dDocName like 'ABCD%' or
) and (
 (dInDate BETWEEN {ts '2017-11-10 12:00:00.000'} AND {ts '2017-11-10 17:00:00.000'})
))
and REV22.dRevClassID = Revisions.dRevClassID)

But it doesn't work, the WCC throw the following error on preview:

WCC error

Server Log:

!csUserEventMessage,weblogic,YYYY!$!csServiceDataException,GET_DATARESULTSET,createResultSetSQL!$!csJdbcGenericError intradoc.common.ServiceException: !csServiceDataException,GET_DATARESULTSET,createResultSetSQL!$ *ScriptStack GET_DATARESULTSET 3:createResultSetSQL,no captured values at intradoc.server.ServiceRequestImplementor.buildServiceException(ServiceRequestImplementor.java:2173) at intradoc.server.Service.buildServiceException(Service.java:2402) at intradoc.server.Service.createServiceExceptionEx(Service.java:2396) at intradoc.server.Service.createServiceException(Service.java:2391) at intradoc.server.ServiceRequestImplementor.handleActionException(ServiceRequestImplementor.java:1816) at intradoc.server.ServiceRequestImplementor.doAction(ServiceRequestImplementor.java:1766) at intradoc.server.Service.doAction(Service.java:564) at intradoc.server.ServiceRequestImplementor.doActions(ServiceRequestImplementor.java:1483) at intradoc.server.Service.doActions(Service.java:559) at intradoc.server.ServiceRequestImplementor.executeActions(ServiceRequestImplementor.java:1415) at intradoc.server.Service.executeActions(Service.java:545) at intradoc.server.ServiceRequestImplementor.doRequest(ServiceRequestImplementor.java:751) at intradoc.server.Service.doRequest(Service.java:1974) at intradoc.server.ServiceManager.processCommand(ServiceManager.java:486) at intradoc.server.IdcServerThread.processRequest(IdcServerThread.java:265) at intradoc.idcwls.IdcServletRequestUtils.doRequest(IdcServletRequestUtils.java:1355) at intradoc.idcwls.IdcServletRequestUtils.processFilterEvent(IdcServletRequestUtils.java:1732) at intradoc.idcwls.IdcIntegrateWrapper.processFilterEvent(IdcIntegrateWrapper.java:223) at sun.reflect.GeneratedMethodAccessor265.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at idcservlet.common.IdcMethodHolder.invokeMethod(IdcMethodHolder.java:87) at idcservlet.common.ClassHelperUtils.executeMethodEx(ClassHelperUtils.java:305) at idcservlet.common.ClassHelperUtils.executeMethodWithArgs(ClassHelperUtils.java:278) at idcservlet.ServletUtils.executeContentServerIntegrateMethodOnConfig(ServletUtils.java:1680) at idcservlet.IdcFilter.doFilter(IdcFilter.java:457) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119) at java.security.AccessController.doPrivileged(Native Method) at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324) at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460) at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103) at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171) at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:163) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119) at java.security.AccessController.doPrivileged(Native Method) at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324) at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460) at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103) at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171) at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71) at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3730) at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3696) at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321) at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120) at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2273) at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2179) at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1490) at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256) at weblogic.work.ExecuteThread.run(ExecuteThread.java:221) Caused by: intradoc.data.DataException: !csJdbcGenericError at intradoc.jdbc.JdbcWorkspace.handleSQLException(JdbcWorkspace.java:2595) at intradoc.jdbc.JdbcWorkspace.createResultSetSQL(JdbcWorkspace.java:830) at intradoc.jdbc.JdbcWorkspace.createResultSetSQL(JdbcWorkspace.java:769) at intradoc.server.Service.createResultSetSQL(Service.java:3742) at sun.reflect.GeneratedMethodAccessor6893.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at intradoc.common.IdcMethodHolder.invokeMethod(IdcMethodHolder.java:86) at intradoc.common.ClassHelperUtils.executeMethodEx(ClassHelperUtils.java:310) at intradoc.common.ClassHelperUtils.executeMethod(ClassHelperUtils.java:295) at intradoc.server.Service.doCodeEx(Service.java:637) at intradoc.server.Service.doCode(Service.java:592) at intradoc.server.ServiceRequestImplementor.doAction(ServiceRequestImplementor.java:1693) ... 48 more

Archiver Log:

Event generated by user 'XXXX' at host 'YYYY'. Edited properties for archive 'AAAA' in collection 'BBBB'. Updated values: aExportQuery = Standard Query ValuePanel UseExportDate 0 AllowExportPublished 0 AllRevisions 1 LatestRevisions 0 NotLatestRevisions 0 MostRecentMatching 0 CurrentIndex 1 Clauses xCollectionID:numberNotEquals:0,dDocName:sqlEq:CCCC CustomQuery Revisions.dID%IN%(select%max%(REV22.dID)%from%Revisions%REV22,%DocMeta, Documents%where%REV22.dID%=%Documents.dID%and%REV22.dID%=%DocMeta.dID%and Documents.dlsPrimary%<>%0%AND%REV22.dReleaseState%<>%'E'%and REV22.dReleaseState%<>%'N'%and%%(

xCollectionID%<>%0%and%( Revisions.dDocName%like%'ABCD#%'%or )%and%( %(dInDate%BETWEEN%{ts%'2017-11-10%12:00:00.000'}%AND%{ts%'2017-11-10%17:00:00.000'}) )) and%REV22.dRevClassID%=%Revisions.dRevClassID) IsCustom 1.

System Audit Log

systemdatabase/6 11.16 09:40:37.958 IdcServer-248219 Reusing connection retrieved from external pool. systemdatabase/6 11.16 09:40:37.959 IdcServer-248219 !csMonitorActiveDbConnections,1 systemdatabase/6 11.16 09:40:37.959 IdcServer-248219 Assigned connection to this thread, took 8.32 ms. connect systemdatabase/6 11.16 09:40:37.959 IdcServer-248219 !csMonitorReadActions,1008597 systemdatabase/6 11.16 09:40:37.968 IdcServer-248219 !csMonitorReadActions,1008598 systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 1.81 ms. SELECT 1 FROM DUAL[Executed. Returned row(s): true] systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 !csDbUnableToCreateResultSet,SELECT Revisions.\, DocMeta.\, Documents.\, RevClasses. systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 FROM Revisions\, DocMeta\, Documents\, RevClasses systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 WHERE Revisions.dID = Documents.dID systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 AND Revisions.dID = DocMeta.dID systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 AND Revisions.dRevClassID = RevClasses.dRevClassID systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 AND Documents.dIsPrimary <> 0 systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 AND Revisions.dReleaseState <> 'E' AND Revisions.dStatus <> 'DELETED' systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 AND ((((((((Revisions.dID IN (select max (REV22.dID) from Revisions REV22\, DocMeta\, systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 Documents where REV22.dID = Documents.dID and REV22.dID = DocMeta.dID and systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 Documents.dlsPrimary <> 0 AND REV22.dReleaseState <> 'E' and systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 REV22.dReleaseState <> 'N' and ( systemdatabase/6 11.16 09:40:37.970 IdcServer-248219
systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 xCollectionID <> 0 and ( systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 Revisions.dDocName like 'ABCD%' or systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 ) and ( systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 (dInDate BETWEEN {ts '2017-11-10 12:00:00.000'} AND {ts '2017-11-10 17:00:00.000'}) systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 )) systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 and REV22.dRevClassID = Revisions.dRevClassID)) AND (Revisions.dPublishState is null OR Revisions.dPublishState = '')))))))) ORDER by Revisions.dDocName!$ORA-00936: missing expression

systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 run: 1.96 ms. total 5.64 ms. [ORA-00936: missing expression systemdatabase/6 11.16 09:40:37.970 IdcServer-248219 ] services/3 11.16 09:40:37.970 IdcServer-248219 !csUserEventMessage,aaaaaaaaa,bbbbbbbbbbbbbbbbbb!$ intradoc.common.ServiceException: !csServiceDataException,GET_DATARESULTSET,createResultSetSQL!$ services/3 11.16 09:40:37.970 IdcServer-248219 *ScriptStack GET_DATARESULTSET services/3 11.16 09:40:37.970 IdcServer-248219 3:createResultSetSQL,no captured values services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceRequestImplementor.buildServiceException(ServiceRequestImplementor.java:2173) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.buildServiceException(Service.java:2402) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.createServiceExceptionEx(Service.java:2396) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.createServiceException(Service.java:2391) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceRequestImplementor.handleActionException(ServiceRequestImplementor.java:1816) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceRequestImplementor.doAction(ServiceRequestImplementor.java:1766) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.doAction(Service.java:564) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceRequestImplementor.doActions(ServiceRequestImplementor.java:1483) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.doActions(Service.java:559) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceRequestImplementor.executeActions(ServiceRequestImplementor.java:1415) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.executeActions(Service.java:545) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceRequestImplementor.doRequest(ServiceRequestImplementor.java:751) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.doRequest(Service.java:1974) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceManager.processCommand(ServiceManager.java:486) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.IdcServerThread.processRequest(IdcServerThread.java:265) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.idcwls.IdcServletRequestUtils.doRequest(IdcServletRequestUtils.java:1355) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.idcwls.IdcServletRequestUtils.processFilterEvent(IdcServletRequestUtils.java:1732) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.idcwls.IdcIntegrateWrapper.processFilterEvent(IdcIntegrateWrapper.java:223) services/3 11.16 09:40:37.970 IdcServer-248219 at sun.reflect.GeneratedMethodAccessor265.invoke(Unknown Source) services/3 11.16 09:40:37.970 IdcServer-248219 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) services/3 11.16 09:40:37.970 IdcServer-248219 at java.lang.reflect.Method.invoke(Method.java:606) services/3 11.16 09:40:37.970 IdcServer-248219 at idcservlet.common.IdcMethodHolder.invokeMethod(IdcMethodHolder.java:87) services/3 11.16 09:40:37.970 IdcServer-248219 at idcservlet.common.ClassHelperUtils.executeMethodEx(ClassHelperUtils.java:305) services/3 11.16 09:40:37.970 IdcServer-248219 at idcservlet.common.ClassHelperUtils.executeMethodWithArgs(ClassHelperUtils.java:278) services/3 11.16 09:40:37.970 IdcServer-248219 at idcservlet.ServletUtils.executeContentServerIntegrateMethodOnConfig(ServletUtils.java:1680) services/3 11.16 09:40:37.970 IdcServer-248219 at idcservlet.IdcFilter.doFilter(IdcFilter.java:457) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119) services/3 11.16 09:40:37.970 IdcServer-248219 at java.security.AccessController.doPrivileged(Native Method) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:163) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119) services/3 11.16 09:40:37.970 IdcServer-248219 at java.security.AccessController.doPrivileged(Native Method) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171) services/3 11.16 09:40:37.970 IdcServer-248219 at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3730) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3696) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2273) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2179) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1490) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256) services/3 11.16 09:40:37.970 IdcServer-248219 at weblogic.work.ExecuteThread.run(ExecuteThread.java:221) services/3 11.16 09:40:37.970 IdcServer-248219 Caused by: intradoc.data.DataException: !csJdbcGenericError services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.jdbc.JdbcWorkspace.handleSQLException(JdbcWorkspace.java:2595) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.jdbc.JdbcWorkspace.createResultSetSQL(JdbcWorkspace.java:830) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.jdbc.JdbcWorkspace.createResultSetSQL(JdbcWorkspace.java:769) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.createResultSetSQL(Service.java:3742) services/3 11.16 09:40:37.970 IdcServer-248219 at sun.reflect.GeneratedMethodAccessor6893.invoke(Unknown Source) services/3 11.16 09:40:37.970 IdcServer-248219 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) services/3 11.16 09:40:37.970 IdcServer-248219 at java.lang.reflect.Method.invoke(Method.java:606) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.common.IdcMethodHolder.invokeMethod(IdcMethodHolder.java:86) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.common.ClassHelperUtils.executeMethodEx(ClassHelperUtils.java:310) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.common.ClassHelperUtils.executeMethod(ClassHelperUtils.java:295) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.doCodeEx(Service.java:637) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.Service.doCode(Service.java:592) services/3 11.16 09:40:37.970 IdcServer-248219 at intradoc.server.ServiceRequestImplementor.doAction(ServiceRequestImplementor.java:1693) services/3 11.16 09:40:37.970 IdcServer-248219 ... 48 more systemdatabase/6 11.16 09:40:37.973 IdcServer-248219 !csMonitorActiveDbConnections,0 systemdatabase/6 11.16 09:40:37.973 IdcServer-248219 release pool connection

1
oraclecontent-management-systemwebcenteroracle-ucm
Open Administration - System Audit Information. Add systemdatabase to Active Sections. Do your export again, and then find the relevant parts of Server Output. Post them here. – Sam
I've added waht you have requested – João Gomes
You posted parts of the log, but that is not the place you want to look at now. On the System Audit Information Page, there is a View System Out close to the top. Use Clear just before your export attempt, and Refresh directly after. – Sam
Added system audit log – João Gomes

1 Answers

1
votes

If you look at your systemdatabase trace you will find two select statements. The first one you can ignore. The second one is what is giving you the problem. It is composed from a standard statement, together with your additions.
If you extract this select statement from the trace and try run it in SQL Developer, you'll find that there is something missing here:

Revisions.dDocName like 'ABCD%' or )