Well, I figured out how the problem originated. I came up with a query to find out the scope of the problem. This query lists all of the objects under a specified path, and all of the folder locations (including of links) of those objects, even if those locations are outside of the specified path.
with
Bind_Parms -- Set client number and folder path here
as (select 100 as Client_Number,
'0100\APPS' AS Folder_Path
from dual),
AllFolderPaths (FOLD_Idnr, FolderPath) AS (
select OH_IDnr,OH_Name
from OH
where OH_OType = 'CLNT'
and OH_Name = '0100'
--between '0000' and '9999'
UNION ALL
select OFS_OH_Idnr_O,
FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
from AllFolderPaths
inner join OFS on FOLD_Idnr = OFS_OH_Idnr_F
inner join OH on OFS_OH_Idnr_O = OH_Idnr
where OH_OType = 'FOLD'
),
child_objects as
(
select OFS_OH_Idnr_O,OFS_Link,OFS_OH_Idnr_F
from OFS
start with OFS_OH_Idnr_F =
(
select OH_IDNR from OH where OH_OType='FOLD'
and OH_Idnr = (
select FOLD_Idnr
from AllFolderPaths
where FolderPath = (Select Folder_Path from Bind_Parms)
)
and OH_CLIENT=(Select Client_Number from Bind_Parms)
)
connect by prior OFS_OH_Idnr_O = OFS_OH_Idnr_F
),
child_objects_and_their_links as (
select OH_Idnr, OH_OType, OH_Name, FOLD_Idnr,
replace(substr(FolderPath,5),'\', '/') as Path,
OFS_Link
from AllFolderPaths
join OFS on FOLD_Idnr=OFS_OH_Idnr_F
join OH on OFS_OH_Idnr_O = OH_Idnr
where OH_OType <> 'FOLD'
and OH_Idnr in (select OFS_OH_Idnr_O from child_objects)
order by OH_Name
),
MainQuery as(
select *
from child_objects_and_their_links
inner join (
select OH_Name OH_Name_P, count(distinct(FOLD_Idnr)) as num_unique_paths
from child_objects_and_their_links
group by OH_Name
) subq
on child_objects_and_their_links.OH_Name = subq.OH_Name_P
)
select
OH_Idnr, OH_OType, OH_Name, FOLD_Idnr, Path, OFS_Link, Num_Unique_Paths
from MainQuery
where Num_Unique_Paths = 1
and OFS_Link=1
order by OH_Name,OFS_Link,Path
This query returned just 7 results - 7 links in a single folder. I realized that the problem likely occurred during XML import, and I quickly confirmed this hunch. The XML file in question is version 3 of my
general-purpose EXEC VARAs. The first part of the XML file follows.
<?xml version="1.0" encoding="ISO-8859-15"?>
<uc-export clientvers="12.0.5+hf.2.build.1638">
<FolderStruct>
<includeExternalObjects>1</includeExternalObjects>
<FOLD name="GENERAL_PURPOSE_EXEC_VARAS" title="">
<JOBS_UNIX id="5812018" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.JOBS"/>
<SCRI id="5808048" link="0" name="UC4.LOOK_UP_FREE_SPACE.SCRI"/>
<SCRI id="5808049" link="0" name="UC4.LOOK_UP_PWP.SCRI"/>
<SCRI id="5796069" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.SCRI"/>
<VARA id="5790060" link="0" name="UC4.AGENT_GROUP_MODE_ALL_TEST.VARA_EXEC"/>
<JOBS_SQL id="5775096" link="0" name="UC4.GP.JOBS_SQL##TEST##"/>
<JOBS_SQL id="5775097" link="0" name="UC4.GP_SQL.JOBS"/>
<VARA id="5775098" link="0" name="UC4.GP_SQL.VARA_EXEC"/>
<VARA id="5775099" link="0" name="UC4.GP_SQL.VARA_EXEC##TEST##"/>
<JOBS_UNIX id="5775100" link="0" name="UC4.GP_UNIX.JOBS"/>
<VARA id="5775101" link="0" name="UC4.GP_UNIX.VARA_EXEC"/>
<VARA id="5775102" link="0" name="UC4.GP_UNIX.VARA_EXEC##TEST##"/>
<FOLD name="COMMON" title="">
<VARA id="5771045" link="1" name="UC4.GET_AGENTGROUP_MODE.VARA_SEC_SQLI"/>
<VARA id="5790030" link="1" name="UC4.GET_OBJ_SUBTYPE.VARA_SEC_SQLI"/>
<JOBI id="5805033" link="1" name="UC4.PARSE_OUTPUT_AND_CREATE_DATA_SEQUENCE.JOBI"/>
<JOBI id="5774078" link="1" name="UC4.READ_JOB_OUTPUT.JOBI"/>
<JOBI id="3141355" link="1" name="UC4.RESOLVE_AGENT_GROUP.JOBI"/>
<JOBI id="5804007" link="1" name="UC4.ERROR_HANDLING_PREPROC.JOBI"/>
<VARA id="5806033" link="1" name="UC4.GET_C_HOSTG_SUBTASKS.VARA_SEC_SQLI"/>
</FOLD>
</FOLD>
<FOLD name="##INCLUDED_EXTERNALS##">
<VARA link="0" name="UC4.GET_AGENTGROUP_MODE.VARA_SEC_SQLI"/>
<VARA link="0" name="UC4.GET_OBJ_SUBTYPE.VARA_SEC_SQLI"/>
<JOBI link="0" name="UC4.PARSE_OUTPUT_AND_CREATE_DATA_SEQUENCE.JOBI"/>
<JOBI link="0" name="UC4.READ_JOB_OUTPUT.JOBI"/>
<JOBI link="0" name="UC4.RESOLVE_AGENT_GROUP.JOBI"/>
<JOBI link="0" name="UC4.ERROR_HANDLING_PREPROC.JOBI"/>
<VARA link="0" name="UC4.GET_C_HOSTG_SUBTASKS.VARA_SEC_SQLI"/>
</FOLD>
</FolderStruct>
Note that exactly seven links (to objects outside of the folder) are included. It appears that when this XML file was imported, the objects were created
as links, but with no home folder. This seems like a bug.