Home » SQL & PL/SQL » SQL & PL/SQL » How to get the table names present in Package/Procedure/Functions (Oracle 9i/10g)
How to get the table names present in Package/Procedure/Functions [message #436773] Mon, 28 December 2009 03:59 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I want to get all the tables which are used either in views/packages/procedures/functions, Do we have any data dictionery which we can use.

I have used the following sql for example.

SELECT DISTINCT text
           FROM user_source
          WHERE NAME IN (
                   SELECT object_name
                     FROM user_objects
                    WHERE object_type IN
                                    ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION'))
            AND text LIKE '%FROM%'


With the above query it would be very difficult.Please let me know for any alternatives.

Regards,
Ashoka BL
Re: How to get the table names present in Package/Procedure/Functions [message #436775 is a reply to message #436773] Mon, 28 December 2009 04:06 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member

Quote:
I want to get all the tables which are used either in views/packages/procedures/functions
.

IS your requirement like You have one table,So now you want the list of object based(dependent) on that table ?
Or IF your input like 'Procedure' then it should display all the tables involved ?
Can you be more clear on this ?
I didn`t get you properly...Any way search for this..


SQL> desc user_dependencies
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(17)
 REFERENCED_OWNER                                   VARCHAR2(30)
 REFERENCED_NAME                                    VARCHAR2(64)
 REFERENCED_TYPE                                    VARCHAR2(17)
 REFERENCED_LINK_NAME                               VARCHAR2(128)
 SCHEMAID                                           NUMBER
 DEPENDENCY_TYPE                                    VARCHAR2(4)


And let me know If i didn`t understood properly...

sriram Smile
Re: How to get the table names present in Package/Procedure/Functions [message #436776 is a reply to message #436773] Mon, 28 December 2009 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are 2 scripts:
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: How to get the table names present in Package/Procedure/Functions [message #436778 is a reply to message #436776] Mon, 28 December 2009 04:40 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thank you Michel for your input ...

Sriram Smile
Re: How to get the table names present in Package/Procedure/Functions [message #436779 is a reply to message #436778] Mon, 28 December 2009 04:54 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ashoka
This one also works for you...

SQL> conn scott/tejajun20@satya
Connected.
SQL> desc ADD_EMP_ORAFAQ
PROCEDURE ADD_EMP_ORAFAQ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_FNAME                        VARCHAR2(10)            IN
 P_LNAME                        VARCHAR2(9)             IN
 P_CITY                         NUMBER(7,2)             IN
 P_ROLLNO                       NUMBER(7,2)             OUT

SQL> select dbms_metadata.get_ddl('PROCEDURE','ADD_EMP_ORAFAQ') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','ADD_EMP_ORAFAQ')
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "SCOTT"."ADD_EMP_ORAFAQ"
( p_fname in EMP.ENAME%type,
p_lname EMP.JOB%type,
p_city EMP.SAL%type,
p_rollno out EMP.SAL%type
)
AS
v_rollno integer;
BEGIN
select max(SAL) into v_rollno from EMP;
p_rollno:=v_rollno+1;
insert into EMP(SAL,ENAME,JOB,DEPTNO)
values
(p_rollno,p_fname,p_lname,p_city);
dbms_output.PUT_LINE('Row Inserted');
END;



1 row selected.
SQL> select REFERENCED_NAME||','||REFERENCED_TYPE
  2  from user_dependencies
  3  where  name  like 'ADD_EMP_ORAFAQ' and type like 'PROCEDURE' and REFERENCED_TYPE like 'TABLE';

REFERENCED_NAME||','||REFERENCED_TYPE
--------------------------------------------------------------------------------
EMP,TABLE

1 row selected.


Here you can find the solutions from Both of us(Michel & ME)

sriram Smile

[Updated on: Mon, 28 December 2009 05:01]

Report message to a moderator

icon14.gif  Re: How to get the table names present in Package/Procedure/Functions [message #436782 is a reply to message #436773] Mon, 28 December 2009 05:24 Go to previous messageGo to next message
nsundar_be
Messages: 2
Registered: December 2009
Junior Member
The below mentioned Query will work fine

select distinct referenced_name
from user_dependencies
where name in

(select distinct object_name
from user_objects
where object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'VIEW'))

and referenced_type = 'TABLE'
Re: How to get the table names present in Package/Procedure/Functions [message #436783 is a reply to message #436782] Mon, 28 December 2009 05:29 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi..Welcome to forum.
But before posting Please read this .
And More over Just giving clue is enough for the OP.
Please use code tags to post your SQL Queries.
This is how your post
SQL> select distinct referenced_name
  2  from user_dependencies
  3  where name in
  4  
SQL> (select distinct object_name
  2  from user_objects
  3  where object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'VIEW'))
  4  
SQL> and referenced_type = 'TABLE' 
SP2-0734: unknown command beginning "and refere..." - rest of line ignored.
SQL> 


Regards
sriram Smile

[Updated on: Mon, 28 December 2009 05:30]

Report message to a moderator

Re: How to get the table names present in Package/Procedure/Functions [message #436784 is a reply to message #436783] Mon, 28 December 2009 05:43 Go to previous messageGo to next message
nsundar_be
Messages: 2
Registered: December 2009
Junior Member
IS this useful....

SQL> select distinct referenced_name
2 from user_dependencies
3 where name in
4 (select distinct object_name
5 from user_objects
6 where object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'VIEW'))
7 and referenced_type = 'TABLE';
Re: How to get the table names present in Package/Procedure/Functions [message #436785 is a reply to message #436773] Mon, 28 December 2009 05:45 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey,

Its been a while since i have visited FORUM, but thanks Razz , I got the correct Answer.

Regards,
Ashoka BL
Re: How to get the table names present in Package/Procedure/Functions [message #436822 is a reply to message #436773] Mon, 28 December 2009 12:34 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Can you please clue us as to which answer was the "correct" answer for you. Others will find it useful to know.

Quote:
Also, about user_dependencies (dba_dependencies); you should know that the behavior of this rowsource has changed over the releases. Oracle has changed what is in the meta data object several times. Basically the issue is one of how deep down does the meta data object trace dependencies.

There was a time when it only went one level down and you had to write code to traverse the dependency chain yourself to get the real answer.

Then Oracle changed it at some point to provide a complete tree; a chain of the dependencies all the way down to the bottom.

Then Oracle went back to the older version were it only goes down one level. I believe this is where it sits now with all currently supported releases. Thus you will have to write some code yourself in order to turn it into a full dependency objects.

Alternatively you can use various tools for getting the information. For example, TOAD has a USES and USED BY tab for showing this. Also there is a set of documentation tools for PLSQL code and the database which are most useful for assessing the level of work required when changing an object, and for generating unique documentation artifacts for PLSQL code and the database.

CLEARSQL
CLEARDB

Not an endorsement by OraFAQ.

But I use these tools regularly. They help me create documentation like no other tool I know. You should see how heads turn in code reviews when I show up with better documentation than the developer has on his own code. I can easily tell which developers are Jerks and which ones are going to survive and grow in the IT field, by how they respond to the documentation I show up with. Those that get offended are problem developers who won't last long, and those that show a real interest after the fact are the ones to keep. Of course my attitude in the review has a lot to do with it as well. I have to make sure not to be snooty or superior because I have learned I am not.

Good luck, Kevin
Previous Topic: Update Table with Other Table
Next Topic: restore the view after update/replace the view
Goto Forum:
  


Current Time: Sun Dec 11 04:22:32 CST 2016

Total time taken to generate the page: 0.10148 seconds