OraFAQ Forum: SQL & PL/SQL » Object Reference Identnifications

Home » SQL & PL/SQL » SQL & PL/SQL » Object Reference Identnifications

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

Object Reference Identnifications [message #603658]

Wed, 18 December 2013 04:18

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

I want to get tables name used in each procedure inside the package
I have tried with DBA_DEPENDENCIES and DBA_PROCEDURES.Count'nt get the details.

Let me know how can i get the details.

Report message to a moderator

Re: Object Reference Identnifications [message #603661 is a reply to message #603658]

Wed, 18 December 2013 04:31

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

Senior Member
Account Moderator

Oracle version?

Report message to a moderator

Re: Object Reference Identnifications [message #603664 is a reply to message #603658]

Wed, 18 December 2013 05:04

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

Using following SQL, I am getting the desired output:

select * from dba_dependencies
where type = 'PROCEDURE'
and owner = 'SCOTT'
and referenced_type = 'TABLE'

Report message to a moderator

Re: Object Reference Identnifications [message #603665 is a reply to message #603664]

Wed, 18 December 2013 05:08

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

that gives stand alone procedures, not procedures in packages.

Report message to a moderator

Re: Object Reference Identnifications [message #603673 is a reply to message #603665]

Wed, 18 December 2013 05:52

mokarem
Messages: 109
Registered: November 2013
Location: Dhaka

Senior Member

Try this....

select *
from dba_dependencies
where type like 'PACKAGE%'
and owner = 'SCOTT'
and referenced_type = 'TABLE'

Report message to a moderator

Re: Object Reference Identnifications [message #603678 is a reply to message #603673]

Wed, 18 December 2013 06:30

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

Senior Member
Account Moderator

No, this still does not give the dependencies for EACH PROCEDURE OF EACH PACKAGE.
Please try to read and understand the question.
And It has already been asked to you to FORMAT your post.

Report message to a moderator

Re: Object Reference Identnifications [message #603680 is a reply to message #603678]

Wed, 18 December 2013 06:48

ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai

Senior Member

I have tried the ways given above it would work if stand alone proc.
As its inside package, need to extract.

Report message to a moderator

Re: Object Reference Identnifications [message #603689 is a reply to message #603680]

Wed, 18 December 2013 08:12

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

Senior Member
Account Moderator

Michel Cadot wrote on Wed, 18 December 2013 11:31

Oracle version?

If you don't answer our question, why should we answer yours?
In addition, this is NOT the first time you post a question and you know that Oracle version is MANDATORY.

Report message to a moderator