Jump to content

Looking for sql (we use Oracle) to determine if a repository object is published or not. Thanks!

Todd Van Valkenburg

Recommended Posts

Thank you. Unfortunately, in 8207.28, I am not seeing consistent results interrogating the ownertype field in the uoa_resources table. Based on the video, unpublished items have ownertype = U. But I am seeing published items with ownertype=U.


FROM webfocus.wf_reposobj wf

   LEFT OUTER JOIN webfocus.uoa_resources uo

ON uo.name = wf.objname

Link to comment
Share on other sites

Make sure to join one to many - the problem is that this table only contains the name of the object without full qualified path or unique ID - it's just a workaround - you might find multiple files with exact same name in uoa_resources that are different files in wf_reposobj.

Sorry did not had any time to tind any other relation yet.

Link to comment
Share on other sites

Todd, I have a fex that makes RESTful Web Service calls to pull information on files and folders in the repository:





Create date

Create user

Modified date

Modified user

Last access date

Last access user

It outputs this as comma delimited data in the browser, which can be copy-pasted into a data file for conventional WebFOCUS reporting.

It could be slow to generate the data, it takes a few minutes to process eleven thousand items for me. Then once you have it in the data file, reporting is fast. You could try it out on a small subset by specifying a recent modifed date.

I'll Message you with the files.

Link to comment
Share on other sites

Thank you. I will take a look. I haven't explored RESTful Web Services yet.

I was hoping for sql, if possible, because of the way I am doing my search. The goal is to find all published repository fex objects in certain workspaces that reference a specific list of mas files. I can do all of this via a sql except the published indicator and then expose this dataset to WebFOCUS for additional analysis. The code/content of the fex is stored in blob field WF_CONTENT_REVS.BCONTENT which I can search to determine if a mas is referenced.

Link to comment
Share on other sites

Based on an old forum post


I was able to create an oracle PL/SQL script that does a case sensitive (or insensitive) text string search on repository objects with the unpublished. Big thanks to Waz's last entry on 3/28/2018 that laid out the code fin MSSQL.

Below is the 1) search script and 2) a function that does the search on the blob contents of the repo objects looking for the string.

Not elegant but seems to work in my testing even when multiple object share the same title in the same repo folder. Use at your own risk:)

WITH published AS (SELECT NVL (p.fex_owner, 'published') AS published_ind, o.handle AS handle FROM webfocus.wf_reposobj o LEFT OUTER JOIN (SELECT r.name AS objname, CASE WHEN NVL (r2.name, ' ') = ' ' THEN ' ' WHEN NVL (r3.name, ' ') = ' ' THEN '/' WHEN NVL (r4.name, ' ') = ' ' THEN '/' || r2.name WHEN NVL (r5.name, ' ') = ' ' THEN '/' || r3.name || '/' || r2.name WHEN NVL (r6.name, ' ') = ' ' THEN '/' || r4.name || '/' || r3.name || '/' || r2.name WHEN NVL (r7.name, ' ') = ' ' THEN '/' || r5.name || '/' || r4.name || '/' || r3.name || '/' || r2.name WHEN NVL (r8.name, ' ') = ' ' THEN '/' || r6.name || '/' || r5.name || '/' || r4.name || '/' || r3.name || '/' || r2.name ELSE '/' || r7.name || '/' || r6.name || '/' || r5.name || '/' || r4.name || '/' || r3.name || '/' || r2.name END AS prt_path, u.description AS fex_owner FROM webfocus.uoa_resources r LEFT OUTER JOIN webfocus.uoa_resources r2 ON r2.id = r.parentid LEFT OUTER JOIN webfocus.uoa_resources r3 ON r3.id = r2.parentid LEFT OUTER JOIN webfocus.uoa_resources r4 ON r4.id = r3.parentid LEFT OUTER JOIN webfocus.uoa_resources r5 ON r5.id = r4.parentid LEFT OUTER JOIN webfocus.uoa_resources r6 ON r6.id = r5.parentid LEFT OUTER JOIN webfocus.uoa_resources r7 ON r7.id = r6.parentid LEFT OUTER JOIN webfocus.uoa_resources r8 ON r8.id = r7.parentid INNER JOIN webfocus.uoa_users u ON r.ownerid = u.id) p ON o.objname = p.objname AND o.prt_path = p.prt_path WHERE p.fex_owner IS NULL)SELECT DISTINCT REPLACE (t2.prt_path, '/WFC/Repository/') AS folder, t4.objdesc AS title, t2.objname AS name, NVL (published.published_ind, 'Not Published') AS "Published Status", t2.createdby, TO_CHAR (t2.createdon, 'YYYY-MM-DD HH24:MI:SS') createdon, t2.lastmodby, TO_CHAR (t2.lastmodon, 'YYYY-MM-DD HH24:MI:SS') lastmodonFROM webfocus.wf_content_revs t1 INNER JOIN webfocus.wf_reposobj t2 ON t1.obj_handle = t2.handle LEFT OUTER JOIN webfocus.wf_objprops t3 ON t2.handle = t3.obj_handle INNER JOIN webfocus.wf_nlsobj t4 ON t2.handle = t4.obj_handle AND t2.def_lng = t4.wf_lng LEFT OUTER JOIN published ON published.handle = t2.handleWHERE -- returns either Y or N. Pass in blob, search string, and Y/N for case sensitivity search where Y=case sensitive string search. wfdev.f_search_blob (t1.bcontent, 'search-string-goes-here', 'N') = 'Y' AND t2.prt_path LIKE '/WFC/Repository%' -- only select published AND published.published_ind IS NOT NULLORDER BY 1, 2;FUNCTION f_search_blob (blob_in IN BLOB, string_in IN VARCHAR, case_sensitive_in IN VARCHAR DEFAULT 'Y') RETURN VARCHARIS sblob_varchar2 VARCHAR2 (32767); nblob_length PLS_INTEGER := 0; sfound VARCHAR2 (1) := 'N'; nsearch_length PLS_INTEGER := 32000; nsearch_start_pos PLS_INTEGER := 1; ssearch_string VARCHAR2 (1000); sexit_loop VARCHAR2 (1) := 'N';BEGIN nblob_length := DBMS_LOB.getlength (blob_in);  -- dont do anything if nothing in blob IF NVL (nblob_length, 0) > 0 THEN -- if blob is <32000 then change the search length IF nblob_length < nsearch_length THEN nsearch_length := nblob_length; END IF;  sblob_varchar2 := UTL_RAW.cast_to_varchar2 ( DBMS_LOB.SUBSTR (blob_in, nsearch_length, nsearch_start_pos));  IF UPPER (case_sensitive_in) <> 'Y' THEN sblob_varchar2 := LOWER (sblob_varchar2); ssearch_string := LOWER (string_in); ELSE ssearch_string := string_in; END IF;  -- do search IF INSTR (sblob_varchar2, ssearch_string, 1, 1) > 0 THEN sfound := 'Y'; END IF;  -- if blob length is longer than 32000, then look thru chunks of 32000 searching for string. -- do loop until either the string is found or reach the end of the blob IF sfound = 'N' AND nblob_length > 32000 THEN LOOP -- increment starting position based on previous search nsearch_start_pos := nsearch_start_pos + nsearch_length;  -- check if search length needs changing IF nsearch_start_pos + nsearch_length > nblob_length THEN -- this will be the last time thru the loop since we have reached the end nsearch_length := nblob_length - nsearch_start_pos; sexit_loop := 'Y'; END IF;  sblob_varchar2 := UTL_RAW.cast_to_varchar2 ( DBMS_LOB.SUBSTR (blob_in, nsearch_length, nsearch_start_pos));  IF UPPER (case_sensitive_in) <> 'Y' THEN sblob_varchar2 := LOWER (sblob_varchar2); END IF;  -- do search IF INSTR (sblob_varchar2, ssearch_string, 1, 1) > 0 THEN sfound := 'Y'; END IF;  IF sfound = 'Y' OR sexit_loop = 'Y' THEN EXIT; END IF; END LOOP; END IF; -- IF sfound = 'N' AND nblob_length > 32000 END IF; -- IF NVL (nblob_length, 0) > 0  RETURN sfound;END f_search_blob;
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...