Trying to find out who calls the function in a package

Hi

I have a funciton inside a packge name - Package_A func Test_A - I want to find out what are all the objects (procedures, packages and functions ) that call this function - is there a way to lookup that information?

Thanks!!
mahjagAsked:
Who is Participating?

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

x
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.

flow01Commented:
I would check the dba_source view

select name, text from dba_source
where upper(text) like '%PACKAGE_A.TEST_A%'
/

If you want to limit that query you could use the
view dba_dependencies to filter on the name of the dba_source.

If you know there are no depencies outside the schema you can also user the
user_source and user_dependencies.
0
Geert GOracle dbaCommented:
toad for oracle from http://www.quest.com/toad-for-oracle/ has such a tab

you can try it for a month
> use database > spool sql to screen to see actual queries
0
Wasim Akram ShaikCommented:
write the procedure name or function name in toad and press F4(describe on that)

a pop up screen will open up in which you have different tabs..

select on the Used By Tab you will see what all are the dependent objects of the current object...

alternatively you can check dba_dependencies view as suggested by flow01 and query over the name and owner to check what all are dependent on a particular object...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
One thing to be aware of with DBA_DEPENDENCIES.  It will only this hard dependencies.  The package name needs to be referenced in the code.  If you are using dynamic SQL anywhere, the dependencies contained in the dynamic SQL will not be listed in DBA_DEPENDENCIES.  The reason is that the dependency cannot be determined at compile time.
0
mahjagAuthor Commented:
Thanks for all the suggestions, I do have dynamic and static reference to the funciton - it will be ideal to find out both in my case
0
johnsoneSenior Oracle DBACommented:
If there are dynamic references, then the only way to find those is to look through DBA_SOURCE as suggested in post 37784641.
0
flow01Commented:
With dynamic references the suggested search is not compleet:

Looking at my own environment the dynamics can also be in the use of function within the same package

v_statement := '
...
for r1 in (select calculation_method from ... )  LOOP
    v_statement :=  ':x := 'PACKAGE_A.'  || r1.calculation_method';
    execute immediate v_statement using out v_calculated_value;
end loop;

So I can't find a specific function like TEST_A : that is the risc of using functions dynamic.
0
mahjagAuthor Commented:
Honestly I am confused with the replies -

First I dont have dba_views in my dev environment so only options are to use user_views or  ALL_views. When I queried user_source or all_source for the name like my search string (function name)I got nothing - then I modified my search string to include the package name.% I got nothing again when I took the dot out meaning query for entire package - the results came back with the source code that ran into 5000 lines -

Obviously I dont want the source code to be listed - what I want is where this package.function name is being called in a different package or procedure - I dont get that even if I use user_dependencies or all_dependencies - not sure where I can start here
0
flow01Commented:
If you don't want the source

select distinct name from dba_source
where upper(text) like '%PACKAGE_A.TEST_A%'
/
instead of

select name, text from dba_source
where upper(text) like '%PACKAGE_A.TEST_A%'
/

If you didn't find anything searching for 'package name.%'   and did find 5000 lines without the .  it's suggests the . and the function where always in a variable (the possibility i mentioned in my previous post):
please check the source(s) you did find for the sentence where the package was found and show us how it looks : maybe we can provide some other suggestions.
Please also show us how your different searches looked (maybe there is another cause you didn't find anything first)
0
Wasim Akram ShaikCommented:
author, don't get confused..

try the instructions provided in the comment in http:#a37785698, it does the same thing, checking the dependencies in user_dependencies..!!

and the dynamic sql part, you can go to that later..don't panic.. experts are listing out all the possible scenarios.. you can consider them checking one by one..!!!
0
mahjagAuthor Commented:
I dont have dba_source select privilege - I only have user_source or all_source - will that help?
0
Wasim Akram ShaikCommented:
yes, that will work, again it has got some restrictions..!!

you can't be able to check the indirect dependencies, if your function or package is being called dynamically in other schemas which has an explicit grant on your object.

but this should be your second concern...first check out the direct dependencies by using either toad or all/dba_dependencies..
0
mahjagAuthor Commented:
OK-here are my questions

I am trying to find out instance of package.function_name being called by same or different objects (procedures, packages or funtions)  in the same schema -

in all /user dependency view is what I got - I dont have dba dependency

how can I query package.function_name dependency in all/user dependency view - I can only query the package_name in entirity and not a package.function_name - am I understanding something wrong?
0
flow01Commented:
No, your understanding is correct : the dependencies are on package level : that why in my first post I already suggested to use it only to limit the search of dba_source. (for you all_source)
0

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
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
Oracle Database

From novice to tech pro — start learning today.