OraFAQ Forum: SQL & PL/SQL » Finding the dependency in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Finding the dependency in Oracle (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Finding the dependency in Oracle [message #571480]

Mon, 26 November 2012 07:38

sss111ind
Messages: 268
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

Report message to a moderator

Re: Finding the dependency in Oracle [message #571483 is a reply to message #571480]

Mon, 26 November 2012 08:07

Solomon Yakobson
Messages: 1398
Registered: January 2010

Senior Member

Check data dictionary view DBA_DEPENDENCIES.

SY.

Report message to a moderator

Re: Finding the dependency in Oracle [message #571486 is a reply to message #571483]

Mon, 26 November 2012 08:25

sss111ind
Messages: 268
Registered: April 2012
Location: India

Senior Member

Hi Solomon,

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

Regards,
Nathan

Report message to a moderator

Re: Finding the dependency in Oracle [message #571487 is a reply to message #571486]

Mon, 26 November 2012 08:28

Solomon Yakobson
Messages: 1398
Registered: January 2010

Senior Member

Be more specific. Define "places".

SY.

Report message to a moderator

Re: Finding the dependency in Oracle [message #571490 is a reply to message #571480]

Mon, 26 November 2012 08:33

Michel Cadot
Messages: 54172
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

Report message to a moderator

Re: Finding the dependency in Oracle [message #571491 is a reply to message #571487]

Mon, 26 November 2012 08:43

sss111ind
Messages: 268
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

Report message to a moderator

Re: Finding the dependency in Oracle [message #571492 is a reply to message #571491]

Mon, 26 November 2012 08:46

Michel Cadot
Messages: 54172
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

Report message to a moderator

Re: Finding the dependency in Oracle [message #571493 is a reply to message #571491]

Mon, 26 November 2012 08:47

sss111ind
Messages: 268
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

Report message to a moderator

Re: Finding the dependency in Oracle [message #571494 is a reply to message #571491]

Mon, 26 November 2012 08:51

Solomon Yakobson
Messages: 1398
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.

Report message to a moderator

Re: Finding the dependency in Oracle [message #571497 is a reply to message #571494]

Mon, 26 November 2012 09:30

sss111ind
Messages: 268
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

Report message to a moderator

Re: Finding the dependency in Oracle [message #571502 is a reply to message #571497]

Mon, 26 November 2012 09:51

Solomon Yakobson
Messages: 1398
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.

Report message to a moderator

Re: Finding the dependency in Oracle [message #571506 is a reply to message #571497]

Mon, 26 November 2012 10:09

Michel Cadot
Messages: 54172
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

Report message to a moderator

Re: Finding the dependency in Oracle [message #571560 is a reply to message #571506]

Tue, 27 November 2012 01:52

sss111ind
Messages: 268
Registered: April 2012
Location: India

Senior Member

Thanks All,

Some doubt got cleared, some remains still.

Regards,
Nathan

Report message to a moderator

Re: Finding the dependency in Oracle [message #571561 is a reply to message #571560]

Tue, 27 November 2012 02:12

Michel Cadot
Messages: 54172
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

some remains still.

Which ones?

Regards
Michel

Report message to a moderator

Previous Topic:	If i am not specify data length in the parameters , how much it will take , please can u help me
Next Topic:	Dynamic Column Generation by using pivot

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 22 01:46:32 CDT 2013

Total time taken to generate the page: 0.12978 seconds