Given a fex name, I would like to be able search the webfocu...

[Todd Van Valkenburg]

Given a fex name, I would like to be able search the webfocus repo database for all occurrences of this fex name to find out which portals are referencing it.

Our repository is stored in an Oracle database.

I was given a search script to search for a text string:

SELECT DISTINCT T1.obj_handle,

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

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

WHERE (( DBMS_LOB.INSTR (T1.BCONTENT, UTL_RAW.CAST_TO_RAW (LOWER('String-goes-here')), 1, 1) > 0 ))

AND T2.PRT_PATH LIKE '/WFC/Repository%'

order by t2.prt_path;

 

This works great to find fex and htm files that have a text string. But it does not search the old portals. And when I say old (there is probably a more appropriate/accurate description for that), but these portals can only be updated in the portal area, and there is not a prtl file in the workspace area.

Hope that makes sense. Or if you know the table name(s) that store this information that would be helpful.

Thanks!

[Toby Mills]

Thats an interesting one Todd.

At first I thought you might just need to change your PRT_PATH to start with /BIP since the old V3 BIPs are in a different subsystem.

But - looks like you need to root around another table:

 

While I dont have time to dig in a lot, you might start with using the same ideas is you have for the /WFC tables and apply those principles here.

Try Switching PRT_PATH to BIP_COMPONENT_PATH

and the BCONTENT to BIP_COMPONENT_DATA.

Note that it appears that theres a row for each component of a bip - so theres a component for the banner and left and right etc You might need to work your way back up to the parent BIP name.

Again I cant give the specific way to do this, but I can tell you your root BIP names seems to be found using:

where bip_component_type =viewroot

Anyway - BIP_COMPONENT looks like the place to dig around.

If you only need to search these occasionally, maybe itd be better to just use the CM Export and search in there

Ill be interested to see what others have for more specifics. I never thought about it before.

[Todd Van Valkenburg]

I started coding the sql using the bip_component table. bip_component records have type values (panel, container, page, canvas, view, viewroot, etc). You have to join bip_component child records to bip_component parent records based on the bip_component_id linking to the bip_component_parent_id. The tricky part is that different portals tend to have different set of record types.

I lost interest after a bit and took your plan B to use change management & export all of our old portals. Extracted the zip and used Notepad++ to search the repos.xml for the fex name. Looking at the xml, you can then easily tell the path to the fex (fexfolder) and the name of the portal (bipPath). I wont need to do this search often so that works for now until I have time to research the sql a little more.

[Todd Van Valkenburg]

Here is a first pass at sql for Oracle that searches the WebFOCUS database for a text string (e.g. fex name) referenced by these old portals (or V3 BIPs as Toby mentioned). Use at your own risk

SELECT CASE

WHEN bc5.bip_component_type = 'viewroot'

THEN

bc5.bip_component_path

WHEN bc6.bip_component_type = 'viewroot'

THEN

bc6.bip_component_path

WHEN bc7.bip_component_type = 'viewroot'

THEN

bc7.bip_component_path

WHEN bc8.bip_component_type = 'viewroot'

THEN

bc8.bip_component_path

WHEN bc9.bip_component_type = 'viewroot'

THEN

bc9.bip_component_path

END portal_name,

SUBSTR (

bc_content.bip_component_properties,

INSTR (bc_content.bip_component_properties, 'fexfolder=') + 11,

INSTR (bc_content.bip_component_properties, 'fexhandle=')

- INSTR (bc_content.bip_component_properties, 'fexfolder=')

- 13) AS fex_folder,

SUBSTR (

bc_content.bip_component_properties,

INSTR (bc_content.bip_component_properties, 'fexhandle=') + 11,

INSTR (bc_content.bip_component_properties, '.fex" name=')

- INSTR (bc_content.bip_component_properties, 'fexhandle=')

- 7) AS fex_name,

SUBSTR (

bc_content.bip_component_properties,

INSTR (bc_content.bip_component_properties, 'fexhandle=') + 11,

INSTR (bc_content.bip_component_properties, '.htm" name=')

- INSTR (bc_content.bip_component_properties, 'fexhandle=')

- 7) AS htm_name,

bc_content.*,

bc1.*,

bc2.*,

bc3.*,

bc4.*,

bc5.*,

bc6.*,

bc7.*,

bc8.*,

bc9.*

FROM webfocus.bip_component bc_content

INNER JOIN webfocus.bip_component bc1

ON bc1.bip_component_id = bc_content.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc2

ON bc2.bip_component_id = bc1.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc3

ON bc3.bip_component_id = bc2.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc4

ON bc4.bip_component_id = bc3.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc5

ON bc5.bip_component_id = bc4.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc6

ON bc6.bip_component_id = bc5.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc7

ON bc7.bip_component_id = bc6.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc8

ON bc8.bip_component_id = bc7.bip_component_parent_id

LEFT OUTER JOIN webfocus.bip_component bc9

ON bc9.bip_component_id = bc8.bip_component_parent_id

-- search for text string below

-- note: set string to %.fex% to see all fex's used across all OLD portals

-- note: set string to %.htm% to see all html pages used across all OLD portals

WHERE bc_content.bip_component_type = 'content'

AND LOWER (bc_content.bip_component_properties) LIKE LOWER ('%.fex%')