Searching oracle DB directory used by PL/SQL packages!

Hi Expert,

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

MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
You can start with:
select * from dba_directories;
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;

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.
slightwv (䄆 Netminder) 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 name from user_source where lower(text) like lower(''%' || DIRECTORY_NAME || '%'')'
from all_directories;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
"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 SpecialistCommented:
Run this from the specific schema after setting serveroutput on -
v_sql varchar2(255);
v_cnt pls_integer := 0;
for d in
(select directory_name
 from all_directories)
 for p in
 (select object_name
  from user_objects
  where object_type = 'PACKAGE BODY')
  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';
  execute immediate v_sql into v_cnt;
  if v_cnt > 0 then
  end if;
  end loop;

end loop;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.