Solved

Trying to find out who calls the function in a package

Posted on 2012-03-29
14
408 Views
Last Modified: 2012-04-18
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!!
0
Comment
Question by:mahjag
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 84 total points
ID: 37784641
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
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 37785369
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37785698
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:johnsone
ID: 37787203
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
 

Author Comment

by:mahjag
ID: 37788343
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
 
LVL 34

Expert Comment

by:johnsone
ID: 37791719
If there are dynamic references, then the only way to find those is to look through DBA_SOURCE as suggested in post 37784641.
0
 
LVL 20

Expert Comment

by:flow01
ID: 37791874
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
 

Author Comment

by:mahjag
ID: 37797179
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
 
LVL 20

Expert Comment

by:flow01
ID: 37797801
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
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 41 total points
ID: 37798117
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
 

Author Comment

by:mahjag
ID: 37799189
I dont have dba_source select privilege - I only have user_source or all_source - will that help?
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37799846
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
 

Author Comment

by:mahjag
ID: 37802592
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
 
LVL 20

Accepted Solution

by:
flow01 earned 84 total points
ID: 37802867
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Schema creation in Oracle12c 6 48
Queries 15 41
ORA-00923: FROM keyword not found where expected 3 66
migration MS SQL database to Oracle 30 62
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question