Since David Briars mentioned a Summit 2016 presentation called 'Reporting from the WebFOCUS Repository' in the post Published status in WebFOCUS Repository, I thought I'd share code I created based on one of the programs from the presentation, 'Search for string in Repository object code'. I found the original report rudimentary so I created my own.

Incidentally, the original report, Search_for_string_in_code.fex, uncovered the fact that many versions of an object (fex, html and probably other object types) are saved in the Repository, but we can only get at the latest from the GUI tools.

The code is below. One can easily build an HTML page that has one text box control for the search string, and one control for the output type.

I'd like to include additional functionality:

• Perform case-insensitive search - this one is difficult for some DBMS since the code is contained in a BLOB
  
• Add Object Status (Published/Unpublished) to report - I have not yet figured out how to determine the status
  
• Add file-type control and filter - this might be handy if the search is to be done on specific file types (fex, htm, sty, etc)
  

 
SET HOLDLIST = PRINTONLY
SET BYDISPLAY = ON
SET PAGE-NUM = NOLEAD
SET HTMLCSS = ON
SET LINES = 99999
-RUN

-DEFAULT &P_SEARCH_STRING  = 'Francis Mariani';
-DEFAULT &WFFMT = 'AHTML';

-SET &P_SEARCH_STRING = LJUST(&P_SEARCH_STRING.LENGTH, &P_SEARCH_STRING, 'A&P_SEARCH_STRING.LENGTH');
-SET &P_SEARCH_STRING = TRUNCATE(&P_SEARCH_STRING);

SET SQLENGINE = SQLORA
SQL SET DEFAULT_CONNECTION WF_Repository
-RUN

SQL
SELECT
DISTINCT
  T2.PRT_PATH
, T2.OBJNAME
, T2.OBJTYPE
, T2.CLASSNAME
, 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') LASTMODON
, T4.OBJDESC
FROM
WF_REPOS.WF_CONTENT_REVS T1

INNER JOIN WF_REPOS.WF_REPOSOBJ T2 ON T1.OBJ_HANDLE = T2.HANDLE
-*LEFT OUTER JOIN WF_REPOS.WF_OBJPROPS T3 ON T2.HANDLE = T3.OBJ_HANDLE
INNER JOIN WF_REPOS.WF_NLSOBJ T4 ON T2.HANDLE = T4.OBJ_HANDLE AND T2.DEF_LNG = T4.WF_LNG

WHERE
DBMS_LOB.INSTR (T1.BCONTENT, UTL_RAW.CAST_TO_RAW ('&P_SEARCH_STRING'), 1, 1) > 0
AND T2.PRT_PATH LIKE '/WFC/Repository%'
;

TABLE ON TABLE HOLD AS TEST/H001
END
-RUN
-IF &LINES EQ 0 GOTO FOC_NODATA;

-SET &I_SEARCH_RESULT_COUNT = &LINES;

-CREATE_REPORT

JOIN CLEAR *
JOIN HANDLE AND DEF_LNG IN WF_REPOSOBJ TO OBJ_HANDLE AND WF_LNG IN WF_NLSOBJ TAG T2 AS J2
-RUN

DEFINE FILE WF_REPOSOBJ
REP_FOLDER/A100 = GETTOK(OBJNAME, 255, 1, ' ', 100, REP_FOLDER);
END
-RUN

TABLE FILE WF_REPOSOBJ
SUM
COMPUTE REP_FOLDER_TITLE/A100 = SUBSTR(1040, OBJDESC, 1, 100, 100, 'A100');
BY REP_FOLDER
WHERE OBJTYPE EQ 101
WHERE PRT_PATH LIKE '/WFC/Repository%'

ON TABLE HOLD AS HFOLDERS FORMAT XFOCUS INDEX REP_FOLDER
END
-RUN

JOIN PRT_FOLDER1 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J1 AS J1
JOIN PRT_FOLDER2 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J2 AS J2
JOIN PRT_FOLDER3 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J3 AS J3
JOIN PRT_FOLDER4 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J4 AS J4
JOIN PRT_FOLDER5 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J5 AS J5
JOIN PRT_FOLDER6 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J6 AS J6
JOIN PRT_FOLDER7 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J7 AS J7
JOIN PRT_FOLDER8 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J8 AS J8
JOIN PRT_FOLDER9 WITH PRT_PATH IN TEST/H001 TO REP_FOLDER IN HFOLDERS TAG J9 AS J9
-RUN

DEFINE FILE TEST/H001
PRT_FOLDER1/A100 = GETTOK(PRT_PATH, 1040, 1, '/', 100, 'A100');
PRT_FOLDER2/A100 = GETTOK(PRT_PATH, 1040, 2, '/', 100, 'A100');
PRT_FOLDER3/A100 = GETTOK(PRT_PATH, 1040, 3, '/', 100, 'A100');
PRT_FOLDER4/A100 = GETTOK(PRT_PATH, 1040, 4, '/', 100, 'A100');
PRT_FOLDER5/A100 = GETTOK(PRT_PATH, 1040, 5, '/', 100, 'A100');
PRT_FOLDER6/A100 = GETTOK(PRT_PATH, 1040, 6, '/', 100, 'A100');
PRT_FOLDER7/A100 = GETTOK(PRT_PATH, 1040, 7, '/', 100, 'A100');
PRT_FOLDER8/A100 = GETTOK(PRT_PATH, 1040, 8, '/', 100, 'A100');
PRT_FOLDER9/A100 = GETTOK(PRT_PATH, 1040, 9, '/', 100, 'A100');
TEMP_PATH4/A102 = IF J4.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J4.REP_FOLDER_TITLE;
TEMP_PATH5/A102 = IF J5.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J5.REP_FOLDER_TITLE;
TEMP_PATH6/A102 = IF J6.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J6.REP_FOLDER_TITLE;
TEMP_PATH7/A102 = IF J7.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J7.REP_FOLDER_TITLE;
TEMP_PATH8/A102 = IF J8.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J8.REP_FOLDER_TITLE;
TEMP_PATH9/A102 = IF J9.REP_FOLDER_TITLE EQ '' THEN '' ELSE '> ' | J9.REP_FOLDER_TITLE;
REP_FOLDER_PATH/A720 =
   J3.REP_FOLDER_TITLE
|| (' ' | TEMP_PATH4)
|| (' ' | TEMP_PATH5)
|| (' ' | TEMP_PATH6)
|| (' ' | TEMP_PATH7)
|| (' ' | TEMP_PATH8)
|| (' ' | TEMP_PATH9);
OBJ_TYPE_DESC/A25 =
     IF OBJTYPE EQ 102                                  THEN 'Reporting Object'
ELSE IF OBJTYPE EQ 103                                  THEN 'WebFOCUS Style Sheet'
ELSE IF OBJTYPE IN (106, 110)                           THEN 'Web Page'
ELSE IF OBJTYPE EQ 107                                  THEN 'Cascading Style Sheet'
ELSE IF OBJTYPE EQ 109 AND PRT_PATH CONTAINS '/~'       THEN 'User Procedure'
ELSE IF OBJTYPE EQ 109                                  THEN 'Procedure'
ELSE IF PRT_PATH CONTAINS 'deferredOutput'              THEN 'Deferred Output'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.js'  OR OBJNAME LIKE '%.JS')   THEN 'JavaScript File'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.mht' OR OBJNAME LIKE '%.MHT')  THEN 'Web Archive'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.htm' OR OBJNAME LIKE '%.HTM')  THEN 'HTML Report'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.pdf' OR OBJNAME LIKE '%.PDF')  THEN 'PDF Report'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.acx' OR OBJNAME LIKE '%.ACX')  THEN 'Metadata Access'
ELSE IF OBJTYPE EQ 4 AND (OBJNAME LIKE '%.mas' OR OBJNAME LIKE '%.MAS')  THEN 'Metadata Master'
ELSE LJUST(9, FPRINT(OBJTYPE, 'I9', 'A9'), 'A9') || (' ' | '(Other)');
END
-RUN

TABLE FILE TEST/H001
PRINT
OBJNAME                 AS 'Resource Name'
PRT_PATH                AS 'Resource Path'
LASTMODBY               AS 'Modified By'
LASTMODON               AS 'Modified On'
CREATEDBY               AS 'Created By'
CREATEDON               AS 'Created On'
OBJ_TYPE_DESC           AS 'Resource Type'

BY REP_FOLDER_PATH      AS 'Repository Folder Path'
BY OBJDESC              AS 'Resource Title'

HEADING
"WebFOCUS Repository Content – String Search"
"Resources containing the string: &P_SEARCH_STRING"

ON TABLE SET STYLE *
-*INCLUDE=your-style-sheet.sty, $

TYPE=REPORT, TITLETEXT='WebFOCUS Repository Content – String Search', $

TYPE=REPORT, COLUMN=REP_FOLDER_PATH, WRAP=OFF, $
TYPE=REPORT, COLUMN=PRT_PATH, WRAP=OFF, $
TYPE=REPORT, COLUMN=LASTMODON, WRAP=OFF, $
TYPE=REPORT, COLUMN=CREATEDON, WRAP=OFF, $

-IF &WFFMT EQ 'HTML' GOTO HTML_STYLE;
-IF &WFFMT EQ 'EXL2K' GOTO EXCEL_STYLE;

TYPE=REPORT,
LINES-PER-PAGE=25,
ARGRAPHENGINE=JSCHART,
ALLOW-SENDEMAIL=OFF,
ALLOW-SAVECHANGE=OFF,
-*ALLOW-PAGINATION=ON,
-*ALLOW-FILTER=ON,
-*ALLOW-FREEZE=ON,
-*ALLOW-SORT=ON,
-*ALLOW-PRINT=ON,
-*ALLOW-CALC=OFF,
-*ALLOW-HIDE=OFF,
-*ALLOW-CHART=OFF,
-*ALLOW-EXPORT=OFF,
-*ALLOW-VISUALIZE=OFF,
-*ALLOW-ROLLUP=OFF,
-*ALLOW-PIVOT=OFF,
-*ALLOW-COMMENTS=OFF,
-*ALLOW-WINDOW=OFF,
-*ALLOW-RESTORE=OFF,
-*ALLOW-ACCORDION=OFF,
-*ALLOW-TOOLS=OFF,
$

TYPE=REPORT,
GRAPHCOLOR=RGB(79 157 190),
GRAPHCOLORNEG=RGB(242 139 32),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
HOVER-BACKCOLOR=RGB(229 239 240),
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
PAGE-LOCATION=BOTTOM,
JUSTIFY=LEFT,
COLOR=RGB(255 255 255),
BACKCOLOR=RGB(204 181 118),
$
TYPE=REPORT,
OBJECT=MENU,
COLOR=RGB(80 80 80),
HOVER-COLOR=RGB(80 80 80),
BACKCOLOR=RGB(229 239 240),
HOVER-BACKCOLOR=RGB(242 247 247),
BORDER-COLOR=RGB(169 169 169),
$
TYPE=REPORT, FREEZE-COLUMN=OBJDESC, $

-GOTO END_STYLE

-HTML_STYLE
-*TYPE=REPORT,HFREEZE=TOP, SCROLLHEIGHT=400, $
-GOTO END_STYLE

-EXCEL_STYLE
TYPE=REPORT,HFREEZE=TOP, SCROLLHEIGHT=1, $
-GOTO END_STYLE

-END_STYLE
ENDSTYLE

ON TABLE PCHOLD FORMAT &WFFMT
END
-RUN

-GOTO REPORT_END

-FOC_NODATA
TABLE FILE CAR
PRINT
COUNTRY NOPRINT
COMPUTE MSG/A200 = 'Search String not found.'; AS ''
WHERE RECORDLIMIT EQ 1;
END
-RUN

-REPORT_END 

This message has been edited. Last edited by: Francis Mariani,

There's a flaw with this thing!

Due to the so-far-undocumented "feature" of multiple versions of a resource, if a resource at one time contained the search string, it will be included in the result set, even if it does not currently contain the string.

Looking into it...

I was told the multiple versions issue existed only for v8.0.08...

Hey guys

I've been using a 3rd party tool for searching for things in the repository and saw this post.

Take a look at this site - it's just a couple of guys I know who decided to make their own 3rd party tool for searching for content.

Take a look at their brochure - I use this just about every week for one thing or another.

TWIGI WebFOCUS Find Tool

I've installed in on 8105 and 8202. Easy import package and tweak to your edasprof to add a couple of global vars.

Be aware though - using this, you'll bypass the UOA security and read the repository directly. Thus if you decide to use this, make sure you are admin to keep your reports secure. It looks through the whole repository.

The guys who wrote it can help you install to try it out.

Good luck!
Toby Mills, CISSP

Hey Toby. Thanks for the link to the TWIGI-BI site/brochure.

It does look like a very nice product; and it is great to see 3rd party tools in the WebFOCUS arena.

No problem David - these guys are looking for more things to add that people like us could use.

Feel free to ask them to think about any extra stuff that we all could find useful.

I used it again today and will be doing some impact analysis for finding SQL server stored procs this week in an attempt to clean up our code and make it more uniform.

Glad you took a look!
Toby