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

[Todd Van Valkenburg]

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

[Patrick Huebgen]

Try

to connect your repository

and

&

to find unpublished reports

Have a great day

Patrick

[Todd Van Valkenburg]

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.

SELECT *

FROM webfocus.wf_reposobj wf

   LEFT OUTER JOIN webfocus.uoa_resources uo

ON uo.name = wf.objname

[Patrick Huebgen]

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.

[Todd Van Valkenburg]

I am looking at two objects with the same name, one is unpublished and one is published. Joining by name is not sufficient. There must be another table that is used to join wf_reposobj to uoa_resources based on a unique key(s). Perhaps that is where the ownerid and/or parentid fields in uoa_resources come into play?

[Patrick Huebgen]

seems not that easy I was not able to find this relationsship - I think WebFOCUS is doing some more internal magic here as it's not doing simple SQL calls - in my experience the number of objects with the same name is relatively low - you might have a few where you need to manually double-check

Patrick

[Todd Van Valkenburg]

We have over 2,100 duplicate objects. I will create a case with ibi to see if there is a way to do this since it would be handy to create repo inventory reports with the published/unpublished indicator.

[Patrick Huebgen]

Ok - souds like your users / developers use very generic report names.

Please keep the community updated if you find a solution.

[David Beagan]

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

Name

Ttitle

Path

Published

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.

[Todd Van Valkenburg]

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.

[Patrick Huebgen]

I was looking into this a while back and I only foumd the solution I described earlier. You could try to open a case maybe there's a hidden solution.

[Todd Van Valkenburg]

I did and waiting for a response.

[Todd Van Valkenburg]

Based on an old forum post

http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8787049286?r=8997063386#8997063386

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;