SQL query to list all objects (and folders) under a given folder

[Michael A. Lowry]

We discovered last week that there is no straightforward way to restrict the number of results normal users can retrieve from searches without also impacting searches performed by administrators and those performed by our own internal processes, The Automation Engine applies the same limit to all searches:

• Searches performed in the Java user interface
• Searches performed in the ECC
• Searches performed by normal users
• Searches performed by administrative users
• Searches performed by an external program, using the SearchObjectclass of the Java Application Interface. 

Regardless of who performs the search or how, the search is subject to the limit specified in GENERIC_SEARCH_LIMIT in UC_SYSTEM_SETTINGS in client 0.

Because the Automation Engine provides no mechanism for finer-grained control, we are trying to find an alternative approach. My first idea was that we could perform our own internal searches using direct SQL queries of the Automation Engine database. I thought before I invested the effort in developing these queries , I should check here first to see if anyone had already done something similar. We need two queries:

1. List allobjectsunder a specified folder, including under all sub-folders.
2. List allfoldersunder a specified folder, including under all sub-folders.

Has anyone else done this before? Thanks in advance for any help.

[Anon Anon]

Would either of these from Philipp Elmer's site be of use as a basis for your needs?

Statistics of UC4-JOBS inside a certain folder

SQL: Folder Path of an Object

[Michael A. Lowry]

Mark Hadler said:

Would either of these from Philipp Elmer's site be of use as a basis for your needs?

Statistics of UC4-JOBS inside a certain folder

SQL: Folder Path of an Object

The queries in those two posts are certainly helpful, and a step in the right direction.

I have now working queries that will recursively list all of the child objects (or folders) of a given folder, given the unique OH_Idnr of the folder. What I need now is a query that will get the unique OH_Idnr of a specified folder path.

E.g, convert a path like

• /0100/APPS/UC4/COMMON

• \0100\APPS\UC4\COMMON

to:

• the exact OH_Idnr of that folder path, e.g., 153402.
  

[Michael A. Lowry]

Related question...

I have a query given to me by Automic that will list all of the folder paths of an object.

select path from
(select level
,ofs_oh_idnr_f as parent
,ofs_oh_idnr_o as child
,oh_name
,substr(oh_name,10,99)
,SYS_CONNECT_BY_PATH( substr(oh_name,10,99) , '/' ) as path
,ofs.ofs_link
from ofs, oh
where oh.oh_idnr = ofs_oh_idnr_f
and ofs_oh_idnr_o = (select oh_idnr from oh where oh_name = 'UC0.MAL.TEST1#1.JOBI')
start with ofs_oh_idnr_f = (select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = 110)
connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f) where ofs_link = 1;

As configured now, with the 'where ofs_link=1' predicate, it will list only the locations of links, and not the object’s home folder.

The problem I am running into now is that I have begun to encounter some situations wherein the path returned by this query contains backslash characters. E.g., the object UC0.MAL.TEST1#1.JOBI has exactly one link, located in
'/APPS/UC0/MAL/TEST1/1/2'. However, the path returned by the query is '/APPS/UC0/MAL/TEST1#A/\1/\2'. Note that the backslashes prefix only the two deepest levels in the folder hierarchy.

I have only begun to see this problem recently, in the past month or so. It never happened before. Any ideas?

[Michael A. Lowry]

I figured out what’s going on. The object IDs in our system have recently grown beyond 7 digits. Automic’s query is based on the assumption that all OH_IDnr values are 7 digits in length. The SUBTR in the original query always starts at position 10.

Comparing the values of OH_NAME for an older folder object and a subfolder created more recently, it’s clear that the assumption that the folder name begins at position 10 does not always hold:

            1234567890
Old folder: 1713155k\TEST1#A
New folder: 10760005k\2

I modified the SQL query so that returns correct results reliably.

select replace(path,'|', '/') from
(
select level
,ofs_oh_idnr_f as parent
,ofs_oh_idnr_o as child
,oh_name
,substr(oh_name,10,99)
,SYS_CONNECT_BY_PATH( substr(oh_name,instr(oh_name,'k\',1)+2) , '|' ) as path
,ofs.ofs_link
from ofs, oh
where oh.oh_idnr = ofs_oh_idnr_f
and ofs_oh_idnr_o =
(select oh_idnr from oh where oh_name = 'UC0.MAL.TEST1#1.CALL')
start with ofs_oh_idnr_f =
(select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = 110)
connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f
)
where ofs_link = 0;

The changes, compared to the query provided by Automic are:

1. Instead of starting the SUBSTR at position 10, the query starts two characters after the position of 'k\'.
2. The SUBSTR is no longer constrained to 99 characters.
3. The path is initially connected using the '|' character, and then this character is replaced with '/' in the outer query. This was necessary because if '/' was used as the separator character forSYS_CONNECT_BY_PATH, the following error was returned:
   ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value
   I do not know why this error does not occur when using a fixed integer as the starting position for SUBSTR
   

[Michael A. Lowry]

I still need an SQL statement that will take a path like /0100/APPS/UC4/COMMON or \0100\APPS\UC4\COMMON and convert it to the exact OH_Idnr of that folder path, e.g., 153402. Can anyone help?

Update: PhilippElmer answered the question above.

[PhilippElmer610374]

Hi Michael,

here’s a query that will return a list of full folder paths and their OH_Idnrs, you can use it as a lookup-table (or inline view) to construct your final query.

with AllFolderPaths (Idnr, FolderPath) AS (
      --find all valid Clients on the UC4 system
      select OH_IDnr,
      --T-SQL:
      --CAST(OH_Name as VARCHAR(MAX))
      --Oracle:
      OH_Name
      from OH
      where OH_OTYPE = 'CLNT'
      and OH_Name between '0000' and '9999'
      UNION ALL
      --find all FOLDers on all clients
      select OFS_OH_IDNR_O,
      --T-SQL:
      --FolderPath + SUBSTRING(OH_Name, CHARINDEX('\', OH_Name), 200)
      --Oracle:
      FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
      from AllFolderPaths
      inner join OFS on Idnr = OFS_OH_Idnr_F
      inner join OH on OFS_OH_Idnr_O = OH_IDNR
      where OH_OTYPE = 'FOLD'
)
select Idnr, FolderPath
from AllFolderPaths
order by FolderPath;

Kind regards,

Philipp

[Michael A. Lowry]

Thanks to PhilippElmer, I now have queries that will list all objects or folders under a specified folder path.

with
-- List all folder paths and their object IDs
AllFolderPaths (Idnr, FolderPath) AS (
      --find all valid Clients on the UC4 system
      select OH_IDnr,
      --T-SQL:
      --CAST(OH_Name as VARCHAR(MAX))
      --Oracle:
      OH_Name
      from OH
      where OH_OTYPE = 'CLNT'
      and OH_Name between '0000' and '9999'
      UNION ALL
      --find all FOLDers on all clients
      select OFS_OH_IDNR_O,
      --T-SQL:
      --FolderPath + SUBSTRING(OH_Name, CHARINDEX('\', OH_Name), 200)
      --Oracle:
      FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
      from AllFolderPaths
      inner join OFS on Idnr = OFS_OH_Idnr_F
      inner join OH on OFS_OH_Idnr_O = OH_IDNR
      where OH_OTYPE = 'FOLD'
),

-- List all child objects under a folder, given the folder OH_IDnr
child_objects as
(
select OFS_OH_IDNR_O
from OFS
start with OFS_OH_IDNR_F =
  (
    select OH_IDNR from OH where OH_OTYPE='FOLD'
    and OH_Idnr = (
     select Idnr
     from AllFolderPaths
     where FolderPath = '0001\APPS\UC0\MAL'
     )
    and OH_CLIENT=0001
  )
connect by prior OFS_OH_IDNR_O = OFS_OH_IDNR_F
),

-- List child object names, IDs, and object types
child_objects_with_names as
(
select OH_name, OH_Idnr, OH_OType from OH,child_objects
where OH.OH_Idnr = child_objects.OFS_OH_IDNR_O
)

-- List all objects under a folder path
select * from child_objects_with_names
where OH_OType <> 'FOLD'

To list all folders under a specified folder path, just replace the final SELECT statement with this:

-- List all folders under a folder path
select Idnr, FolderPath
from AllFolderPaths
where FolderPath like '0001\APPS\UC0\MAL\%'

The bits in bold italics obviously need to be changed depending on the client number and path in which you want to search.

Thanks again to Philipp for his valuable assistance.

[Michael A. Lowry]

This morning I resumed testing of the updated SQL query (above) for listing an object’s home folder location, and the locations of any links. I saw something I had not seen before:

                                                                     

LEVEL	PARENT	CHILD	OH_NAME	OBJ_NAME	PATH	OFS_LINK
5	1581937	2332310	1078961k\TEST#1	TEST#1	|100|APPS|UC0|MAL|TEST#1	0
5	2673324	2332310	1078961k\TEST_LINKS	TEST_LINKS	|100|APPS|UC0|MAL|TEST_LINKS	1
8	2575757	2332310	2575755k\TEST1	TEST1	|100|SYSTEM|USERS|46JZ8/CORP|UC0|MAL|TEST|TEST1	1
7	2575858	2332310	2575856k\TEST1	TEST1	|100|SYSTEM|USERS|46JZ8/CORP|MAL|TEST|TEST1	1

The first result is the home folder location of the object. The second result corresponds to a link. From what I have been able to discover, the third and fourth results correspond to favorites. (In the folder hierarchy, these links are located under my user ID, and I can see these folder structured replicated in the User Interface under my favorites.)

The SYS_CONNECT_BY_PATH does not work as expected in situations wherein OH_Name does not contain the string  \k. Any ideas for a good way to exclude favorites from the results?

[Michael A. Lowry]

I modified the query a bit to build the path differently in the case of favorites, and to identify such links based on the path.

select path, ofs_link,
case when instr(path, 'SYSTEM|USERS',1) = 0
  then 0
  else 1
end as favorite
from (
select level
,ofs_oh_idnr_f as parent
,ofs_oh_idnr_o as child
,oh_name
,case when oh_otype='FOLD'
   then substr(oh_name,instr(oh_name,'k\',1)+2)
   else oh_name
   end as obj_name
,SYS_CONNECT_BY_PATH(
   case when oh_otype='FOLD'
   then substr(oh_name,instr(oh_name,'k\',1)+2)
   else oh_name
   end,'|') as path
,ofs.ofs_link
from ofs, oh
where oh.oh_idnr = ofs_oh_idnr_f
--and oh_otype = 'FOLD'
and ofs_oh_idnr_o =
(select oh_idnr from oh where oh_name = 'UC0.MAL.TEST1#1.CALL')
start with ofs_oh_idnr_f =
(select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = 100)
connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f
)
where ofs_link = 1
--and favorite = 0

This works until I uncomment the last line. For some reason, then Oracle returns the error:

ORA-00904: "FAVORITE": invalid identifier

I have no idea why this error is occurring — maybe anOracle bug related to SYS_CONNECT_BY_PATH. Next, I’ll try modifyingPhilipp Elmer’srecursive query, to see if I have more success. I’m sure there’s a more elegant way to do it!

[Michael A. Lowry]

Ok, this works:

with AllFolderPaths (Idnr, FolderPath) AS (
  select OH_IDnr,
  OH_Name
  from OH
  where OH_OTYPE = 'CLNT'
  and OH_Name between '0000' and '9999'
  UNION ALL
  select OFS_OH_IDNR_O,
  FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
  from AllFolderPaths
  inner join OFS on Idnr = OFS_OH_Idnr_F
  inner join OH on OFS_OH_Idnr_O = OH_IDNR
  where OH_OTYPE = 'FOLD'
)

select * from AllFolderPaths
where Idnr in
( select OFS_OH_IDNR_F
  from OFS
  where OFS_OH_Idnr_O =
  ( select OH_IDNR
    from oh
    where oh_name = 'UC0.MAL.TEST1#1.CALL'
  )
  and OFS_Link = 1
)

If anyone has suggestions for further improvements, please feel free to post them here.

[Michael A. Lowry]

Here is another query that works, based on Matthew Bailey’s very handy query from a couple of years ago.

-- Based on query provided by Matthew Bailey at Automic
-- https://community.automic.com/discussion/comment/2673/#Comment_2673
with object_paths as (
select (select OH_Client from oh where oh_idnr = child)  Client
     , (select OH_OType  from oh where oh_idnr = child)  Type
     , (select OH_Name   from oh where oh_idnr = child)  Object_Name
     , Path
     , ofs_link                                          Link
     ,case when instr(Path, 'SYSTEM|USERS',1) = 0
        then 0
        else 1
      end as Favorite
  from (select level
             , ofs_oh_idnr_f                             as parent
             , ofs_oh_idnr_o                             as child
             , oh_name
             , SYS_CONNECT_BY_PATH(
               case when oh_otype='FOLD'
                 then substr(oh_name,instr(oh_name,'k\',1)+2)
                 else oh_name
               end,'|') as Path
             , ofs_link
          from ofs
             , oh
         where oh_idnr            = ofs_oh_idnr_f
         start with ofs_oh_idnr_f = (select oh_idnr
                                       from oh
                                      where oh_otype  = 'CLNT'
                                        and oh_client = &CLIENT)
         connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f)
)
select * from object_paths
where Object_Name                = &OBJECT
--and Link                         = &Link
--and Favorite                     = &Favorite
order by Path, Type, Object_Name, Link, Favorite

Note that this query contains the assumption that all USER objects are stored under the folder path/SYSTEM/USERS. Links appearing under this path are assumed to befavorites. I opted to use the vertical bar character ‘|’ as a path delimiter because it is a character that can appear in neither object names nor folder names. (The forward slash character ‘/’ can appear in the names of USER objects.)

[Amarnath Reddy]

Please help me with the query to find out details of the objects executed between the specific time frame.

[Michael A. Lowry]

Amarnath Reddy said:

Please help me with the query to find out details of the objects executed between the specific time frame.

I suggest you start a new topic for this question.

[Uday Giri Vytla]

HI Michael,

Can you please help here to get the objects.

I am not able to execute the query given above, its throwing syntax error.

The point here i mention is that how we can get object without a table  "AllFolderPaths" where you have used below 

select * from AllFolderPaths
where Idnr in
( select OFS_OH_IDNR_F
  from OFS
  where OFS_OH_Idnr_O =
  ( select OH_IDNR
    from oh
    where oh_name = 'UC0.MAL.TEST1#1.CALL'
  )
  and OFS_Link = 1
)

[Michael A. Lowry]

AllFolderPaths is a common table expression earlier in the same SQL statement. See this comment.

[Matthew Bailey]

Incorporating Philipp Elmer's query, this returns a recursive list of all objects under given folder (&W_FOLDER#) in current client:

with AllFolderPaths (Idnr, FolderPath) AS (
     --find all valid Clients on the UC4 system
     select OH_IDnr
         -- T-SQL:
         -- , CAST(OH_Name as VARCHAR(MAX))
         -- Oracle:
          , OH_Name
       from OH
      where OH_OTYPE    = 'CLNT'
        and OH_Name     = '&$CLIENT#'
     UNION  ALL
     --find all FOLDers on all clients
     select OFS_OH_IDNR_O
         -- T-SQL:
         -- , FolderPath + SUBSTRING(OH_Name, CHARINDEX('\', OH_Name), 200)
         -- Oracle:
          , FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
       from AllFolderPaths
            inner join OFS on Idnr          = OFS_OH_Idnr_F
            inner join OH on OFS_OH_Idnr_O  = OH_IDNR
      where OH_OTYPE    = 'FOLD'
     )
select -- Idnr,                   -- Folder Identifier
       OH_IDnr                    -- Object Identifier
     , FolderPath
     , SUBSTRING(OH_Name, CHARINDEX('\', OH_Name) + 1, 200) ObjectName
  from AllFolderPaths
     , OFS
     , OH
 where FolderPath    like '&$CLIENT#\&W_FOLDER#%'
   and idnr             = OFS_OH_Idnr_F
   and OH_Idnr          = OFS_OH_Idnr_O
   and OH_OType        != 'FOLD'
 order by FolderPath, ObjectName

[Christoph Rekers at Tricise]

This works for me under MSSQL:

WITH AllFolderPaths (Idnr, FolderPath) AS (
    -- Anchor: Find all valid Clients on the UC4 system
    SELECT 
        OH_IDnr,
        CAST(OH_Name AS NVARCHAR(MAX)) AS FolderPath -- Explicit conversion to NVARCHAR(MAX)
    FROM OH
    WHERE OH_OTYPE = 'CLNT'
      AND OH_Name = '1000'
    UNION ALL
    -- Recursive: Find all FOLDers on all clients
    SELECT 
        OFS.OFS_OH_IDnr_O,
        CAST(FolderPath + '\' + SUBSTRING(OH_Name, CHARINDEX('\', OH_Name), 200) AS NVARCHAR(MAX)) -- Ensure the same type
    FROM AllFolderPaths
    INNER JOIN OFS ON AllFolderPaths.Idnr = OFS.OFS_OH_Idnr_F
    INNER JOIN OH ON OFS.OFS_OH_Idnr_O = OH_IDnr
    WHERE OH_OTYPE = 'FOLD'
)
SELECT 
    -- OH.OH_IDnr, -- Object Identifier
    FolderPath,
    oh.OH_otype,
    SUBSTRING(OH_Name, CHARINDEX('\', OH_Name) + 1, 200) AS ObjectName
    
FROM AllFolderPaths
INNER JOIN OFS ON AllFolderPaths.Idnr = OFS.OFS_OH_Idnr_F
INNER JOIN OH ON OFS.OFS_OH_Idnr_O = OH_IDnr
WHERE FolderPath LIKE '1000\\%'
  AND OH_OType != 'FOLD'
ORDER BY FolderPath, ObjectName;

------------------------------
--------------------------------------------------------
Automic Consultant and Trainer since 2000
--------------------------------------------------------
now Tricise
------------------------------