I would like to get the name of packages that refers a list of directory. I tried querying the ALL_SOURCE and was able to get it by hard coding the name of directories like,
select *
from all_source
Where upper(text) like '%XXHRMS_DIR%'
OR upper(text) like '%XXARUPLOAD%'
and type='PACKAGE BODY';
I have list of 10-15 directories and instead of hard coding the directory names, will it be possible to use a sub-query which would return the object_name from all_objects where the object_type is DIRECTORY using regexp_like.
SELECT *
FROM all_source
WHERE REGEXP_LIKE (UPPER (TEXT), '^.[XXAPUPLOAD.|.XXFA_DIR].')
AND TYPE = 'PACKAGE BODY'
The above query with REGEXP_LIKE doesn't work - it returns all the package names and I am not sure how to use a sub-query to substitute the ALL_OBJESTS.object_name instead of hard coding the directory names one by one.
Expected Output : The output should be package body names which refers the given set of directories in the code.