Web Center Content - Database Views to answer simple questions

Environment / assumptions for this post:

• Web Center Content 11.1.1.8
• Oracle DB 11.2.0.4

This started as a way to answer the question: “do we have any documents bigger than x number of Mb”.  Pretty simple question, not necessarily the easiest to answer. There are some reports and statistics available through the UCM Admin Console itself, but I wanted a simple way to provide this sort of data to another tool, say, BI Publisher.  With that, users can create their own reports and schedule them.

We actually used this sort of approach before on one of our major application projects, which was based on the old Oracle Portal 11g. By building over a dozen views, we were able to make internal tables such as:
WWV_THINGS
WWV_THINGATTRIBUTES
etc
...much more accessible. Then, by building views (and ADF View Objects) on top of those basic views, it became easy to extract information for particular screens and use cases.

UCM_FOLDERS View

First, start with a basic view which assembles folder/location information, as well as things like creator, last modified, etc. We'll call it UCM_FOLDERS.

CREATE OR REPLACE FORCE VIEW "DEV_OCS"."UCM_FOLDERS_VW" ("LEVEL","PATH","FOLDER","CREATED","CREATOR","LAST_MODIFIED","LAST_MODIFIED_BY","SECURITY_GROUP","COLLECTION_ID") AS
select level "LEVEL",
       SYS_CONNECT_BY_PATH(dcollectionname, '/') "PATH",
       dcollectionname "FOLDER",
       to_char(dcreatedate,'yyyy-mm-dd hh24:mi:ss') "CREATED",
       dcollectioncreator "CREATOR",
       to_char(dlastmodifieddate,'yyyy-mm-dd hh24:mi:ss') "LAST_MODIFIED",
       dcollectionmodifier "LAST_MODIFIED_BY",
       dsecuritygroup "SECURITY_GROUP",
       dcollectionid "COLLECTION_ID"
from collections
start with dparentcollectionid=-1
CONNECT BY nocycle prior dcollectionid=dparentcollectionid and dparentcollectionid!=-1;

Some examples of queries you could do:

select * from ucm_folders_vw where folder='jjames';
select * from ucm_folders_vw where path like '/Contribution Folders%';
select * from ucm_folders_vw where creator='jjames';


UCM_DOC_FOLDERS View

Now, we can build on that. In this case, we are interested in things like document name and size. Let's call it UCM_DOC_FOLDERS. We'll also include a few important columns like DOC_ID and COLLECTION_ID, which may be useful if we need to link to other tables.

I've limited to Primary versions only.

CREATE OR REPLACE FORCE VIEW "DEV_OCS"."UCM_DOC_FOLDERS_VW" ("DOC_ID","DOC_NAME","DOC_SIZE","FOLDER_NAME","LEVEL","PATH","CREATED","CREATOR","LAST_MODIFIED","LAST_MODIFIED_BY","SECURITY_GROUP","COLLECTION_ID") AS
select doc.did "DOC_ID",
       doc.doriginalname "DOC_NAME",
       doc.dfilesize "DOC_SIZE",
       collec.dcollectionname "FOLDER_NAME",
       folders.path_level "LEVEL",
       folders.path "PATH",
       to_char(dcreatedate,'yyyy-mm-dd hh24:mi:ss') "CREATED",
       dcollectioncreator "CREATOR",
       to_char(dlastmodifieddate,'yyyy-mm-dd hh24:mi:ss') "LAST_MODIFIED",
       dcollectionmodifier "LAST_MODIFIED_BY",
       dsecuritygroup "SECURITY_GROUP",
       collec.dcollectionid "COLLECTION_ID"
from documents doc
join docmeta meta on (doc.did=meta.did)
join collections collec on (meta.xcollectionid=collec.dcollectionid)
join ucm_folders_vw folders on (collec.dcollectionid=folders.collection_id)
where doc.disprimary=1;

Some examples of queries you could do with this:

select doc_name,doc_size,path,last_modified
from ucm_doc_folders_vw 
where creator='jjames' and doc_name like '%test%';