Searching oracle DB directory used by PL/SQL packages!

MIHIR KAR
MIHIR KAR used Ask the Experts™
on
Hi Expert,

Could anyone please give me light how to find out Oracle Directories used by PL/SQL Packages in a specific schema?

Thanks,
Mihir
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark GeerlingsDatabase Administrator

Commented:
You can start with:
select * from dba_directories;
or:
select * from user_directories:
(as the schema owner of the PL\SQL objects).

These will show you the logical directories that are defined.

To find the ones that are used by PL\SQL procedures will take queries like these:

select owner, name, line, text from dba_source
where upper(text) like '%UTL_FILE.FOPEN%'
order by owner, name, line;

or:
select user, name, line, upper(text) from user_source
where upper(text) like '%UTL_FILE.FOPEN%'
order by name, line;
(as the schema owner of the PL\SQL objects).

If the records returned by these queries look like this:
owner, procedure_name, line#,'UTL_FILE.FOPEN('

You will need to query dba_source or user_source for that same owner and object_name but for the line# one higher than the line number you have, to see the actual logical directory name.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I don't think you can just go by UTL_FILE.  There are several other calls I can think of that reference directories.  BFILENAME for example.

I was surprised when I tried to use it but I don't see a user_directories view in the database or docs.

Here is what I can think of for a quick and dirty approach:
select 
extractvalue(
dbms_xmlgen.getxmltype(
                 'select name from user_source where lower(text) like lower(''%' || DIRECTORY_NAME || '%'')'
               )
,'/ROWSET/ROW/NAME')
from all_directories;

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
"user_directories"?  My mistake, slightwv is correct.  Use: all_directories instead.  This is one of the exceptions in Oracle's data dictionary in the set of dba_, all_, and user_ objects, where many of the views exist with all three of these prefixes.   There is a dba_directories and an all_directories, but no: user_directories.
awking00Information Technology Specialist

Commented:
Run this from the specific schema after setting serveroutput on -
declare
v_sql varchar2(255);
v_cnt pls_integer := 0;
begin
for d in
(select directory_name
 from all_directories)
loop
 for p in
 (select object_name
  from user_objects
  where object_type = 'PACKAGE BODY')
  loop
  v_sql := 'select count(*) from user_source where name = '||chr(39)||p.object_name||chr(39)||' and instr(text,'||chr(39)||d.directory_name||chr(39)||') > 0';
--dbms_output.put_line(v_sql);
  execute immediate v_sql into v_cnt;
  if v_cnt > 0 then
    dbms_output.put_line(v_sql);
  end if;
  end loop;

end loop;
end;
/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial