Home » SQL & PL/SQL » SQL & PL/SQL » Finding the dependency in Oracle (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)
Finding the dependency in Oracle [message #571480] Mon, 26 November 2012 07:38 Go to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi All,

Is it possible to find the dependency of a Function whether it is used somewhere in the package or procedure or some other functions?

Suppose I have function named get_valid_loc , Now my question is how to find out in which place this function has been used.

Regards,
Nathan
Re: Finding the dependency in Oracle [message #571483 is a reply to message #571480] Mon, 26 November 2012 08:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Check data dictionary view DBA_DEPENDENCIES.

SY.
Re: Finding the dependency in Oracle [message #571486 is a reply to message #571483] Mon, 26 November 2012 08:25 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi Solomon,

It's not showing all the places where the function has been used.

Regards,
Nathan
Re: Finding the dependency in Oracle [message #571487 is a reply to message #571486] Mon, 26 November 2012 08:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Be more specific. Define "places".

SY.
Re: Finding the dependency in Oracle [message #571490 is a reply to message #571480] Mon, 26 November 2012 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Procedures and functions inside a package are NOT object by themselves and so you have no dependency upon them but on the whole package.

Regards
Michel
Re: Finding the dependency in Oracle [message #571491 is a reply to message #571487] Mon, 26 November 2012 08:43 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi Solomon,

When I queried like this referenced_name field is showing few lines where it is used but not all.
select * from DBA_DEPENDENCIES where NAME='GET_VALID_LOC'


Regards,
Nathan
Re: Finding the dependency in Oracle [message #571492 is a reply to message #571491] Mon, 26 November 2012 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 26 November 2012 15:33
Procedures and functions inside a package are NOT object by themselves and so you have no dependency upon them but on the whole package.

Regards
Michel


Show us what you get and what you expect.
Post a test case that we can reproduce.

Regards
Michel
Re: Finding the dependency in Oracle [message #571493 is a reply to message #571491] Mon, 26 November 2012 08:47 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi Micheal,

This 'GET_VALID_LOC' function is standalone function ,but it has been used inside the different packages. So How can I check the dependencies.

Regards,
Nathan
Re: Finding the dependency in Oracle [message #571494 is a reply to message #571491] Mon, 26 November 2012 08:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
First of all, DBA_DEPENDENCIES is not showing ANY lines. It shows dependencies between objects. And scondly your query is wrong. It shows what objects GET_VALID_LOC depends on. Now what object depend on GET_VALID_LOC. Use:

select * from DBA_DEPENDENCIES where REFERENCED_OWNER = 'GET_VALID_LOC-owner' AND REFERENCED_NAME = 'GET_VALID_LOC'


SY.
Re: Finding the dependency in Oracle [message #571497 is a reply to message #571494] Mon, 26 November 2012 09:30 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Hi Solomon,

select * from DBA_DEPENDENCIES where NAME='GET_VALID_LOC';

OWNER	NAME	         TYPE	  REFERENCED_OWNER	REFERENCED_NAME	   REFERENCED_TYPE	REFERENCED_LINK_NAME	DEPENDENCY_TYPE
APPS	GET_VALID_LOC	FUNCTION	SYS	        STANDARD	            PACKAGE		                  HARD



I have queried like this I got to know that this function has been used by a package from the (REFERENCED_TYPE), but REFERENCED_NAME is showing STANDARD , though it's been used by the package called Ifile_pkg.

Regards,
Nathan
Re: Finding the dependency in Oracle [message #571502 is a reply to message #571497] Mon, 26 November 2012 09:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
sss111ind wrote on Mon, 26 November 2012 10:30
I have queried like this



Did you read my reply? Again, use:

select * from DBA_DEPENDENCIES where REFERENCED_OWNER = 'GET_VALID_LOC-owner' AND REFERENCED_NAME = 'GET_VALID_LOC'


SY.
Re: Finding the dependency in Oracle [message #571506 is a reply to message #571497] Mon, 26 November 2012 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or use the scripts at the following links:

http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1236
http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1237

Regards
Michel
Re: Finding the dependency in Oracle [message #571560 is a reply to message #571506] Tue, 27 November 2012 01:52 Go to previous messageGo to next message
sss111ind
Messages: 480
Registered: April 2012
Location: India
Senior Member

Thanks All,

Some doubt got cleared, some remains still.

Regards,
Nathan
Re: Finding the dependency in Oracle [message #571561 is a reply to message #571560] Tue, 27 November 2012 02:12 Go to previous message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
some remains still.


Which ones?

Regards
Michel
Previous Topic: If i am not specify data length in the parameters , how much it will take , please can u help me
Next Topic: Masking in Oracle
Goto Forum:
  


Current Time: Thu Sep 18 19:25:05 CDT 2014

Total time taken to generate the page: 0.09332 seconds