Home » SQL & PL/SQL » SQL & PL/SQL » object dependencies (merged)
object dependencies (merged) [message #380510] Mon, 12 January 2009 02:50 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

i want to know using a procedure how many tables are been referenced in it.i know the concept but tell me the query to use
Re: querying to list object dependencies [message #380512 is a reply to message #380510] Mon, 12 January 2009 03:06 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to the concept, what query did you manage to write?
Re: querying to list object dependencies [message #380513 is a reply to message #380512] Mon, 12 January 2009 03:08 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
I WANT TO KNOW WHICH ARE ALL THE TABLES REFERENCED HAVING DEPENDENCIES THROUGH A STORED PROCEDURE
Re: querying to list object dependencies [message #380514 is a reply to message #380510] Mon, 12 January 2009 03:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well, Oracle has a dictionary table that might help you out. It is called ALL_DEPENDENCIES, but you might want to opt for USER_DEPENDENCIES.

MHE
Re: querying to list object dependencies [message #380515 is a reply to message #380514] Mon, 12 January 2009 03:10 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
YEAH I WANT THE QUERY TO DISPLAY ALL THE DEPENDENT OBJECTS REFERENCED IN A STORED PROCEDURE
Re: querying to list object dependencies [message #380517 is a reply to message #380514] Mon, 12 January 2009 03:10 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

Try This
select name, type, owner
from all_dependencies
where referenced_owner = :OWNER
and referenced_name = :table_name;
Re: querying to list object dependencies [message #380518 is a reply to message #380515] Mon, 12 January 2009 03:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajasekhar857 wrote on Mon, 12 January 2009 10:10
YEAH I WANT THE QUERY TO DISPLAY ALL THE DEPENDENT OBJECTS REFERENCED IN A STORED PROCEDURE

Do you know that posting in all caps is considered rude in forums?
It is the equivalent of shouting.
Re: querying to list object dependencies [message #380521 is a reply to message #380517] Mon, 12 January 2009 03:18 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
IAM UNABLE TO GET IT .IT IS SHOWING NO ROWS SELECTED.AND I WANT THE RESULT HROUGH A PROCEDURE NOT THROUGH A TABLE NAME
Re: querying to list object dependencies [message #380523 is a reply to message #380521] Mon, 12 January 2009 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and STOP POSTING IN UPPER CASE.

Regards
Michel
Re: querying to list object dependencies [message #380524 is a reply to message #380521] Mon, 12 January 2009 03:30 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

You can use this ...... Smile

declare
s_owner varchar2(30) := upper('&owner');
s_name varchar2(30) := upper('&obj_name');
--
subtype uid is sys.user$.user#%type;
subtype unam is sys.user$.name%type;
subtype oid is sys.obj$.obj#%type;
subtype onam is sys.obj$.name%type;
subtype otyp is sys.obj$.type#%type;
subtype ostat is sys.obj$.status%type;
--
d_name onam;
d_typ otyp;
d_oid uid;
d_onam unam;
d_stat varchar2(30);
d_rem_own unam;
d_link varchar2(31);
--
cursor get_user ( own_id in uid, own_name in unam ) is
select user# u_id, name u_name
from sys.user$
where (user# = own_id or own_id is null)
and (name = own_name or own_name is null);
--
cursor get_obj (own_id in uid, obj_id in oid, obj_name in onam) is
select obj# o_id, name o_name, type# o_type,
/* decode(status, 0, 'N/A', 1, 'VALID', 'INVALID') status */
decode(status, 0, null, 1, null, 'VALID') status
from sys.obj$
where owner# = own_id
and (obj# = obj_id or obj_id is null)
and (name like obj_name or obj_name is null);
--
cursor get_dependencies (obj_id in oid) is
select level, p_obj# d_id
from sys.dependency$
connect by prior p_obj# = d_obj#
start with d_obj# = obj_id;
--
function object_type ( obj_typ in sys.obj$.type#%type ) return varchar2 is
obj_desc varchar2(30) := null;
begin
if (obj_typ = 0) then
obj_desc := 'NEXT OBJECT';
elsif (obj_typ = 1) then
obj_desc := 'INDEX';
elsif (obj_typ = 2) then
obj_desc := 'TABLE';
elsif (obj_typ = 3) then
obj_desc := 'CLUSTER';
elsif (obj_typ = 4) then
obj_desc := 'VIEW';
elsif (obj_typ = 5) then
obj_desc := 'SYNONYM';
elsif (obj_typ = 6) then
obj_desc := 'SEQUENCE';
elsif (obj_typ = 7) then
obj_desc := 'PROCEDURE';
elsif (obj_typ = Cool then
obj_desc := 'FUNCTION';
elsif (obj_typ = 9) then
obj_desc := 'PACKAGE';
elsif (obj_typ = 10) then
obj_desc := 'NON-EXISTENT';
elsif (obj_typ = 11) then
obj_desc := 'PACKAGE BODY';
elsif (obj_typ = 12) then
obj_desc := 'TRIGGER';
else
obj_desc := 'UNDEFINED';
end if;
return ( obj_desc );
end;

begin
for own_rec in get_user ( null, s_owner ) loop
for obj_Rec in get_obj ( own_rec.u_id, null, s_name ) loop
-- dbms_output.put_line('obj# = ' || obj_rec.o_id );
dbms_output.put_line('.' );
dbms_output.put_line('.' ||
rpad(object_type ( obj_rec.o_type ),12) || ' ' ||
own_rec.u_name || '.' || obj_rec.o_name ||
' ' || obj_rec.status );
for dpnd_rec in get_dependencies ( obj_rec.o_id ) loop
--
-- dbms_output.put_line('obj# = ' || dpnd_rec.d_id );
for dobj_rec in (
select name d_name, owner# d_oid, type# d_typ, remoteowner d_rem_own,
decode( linkname, null, null, '@' || linkname) d_link,
/* decode(status, 0, 'N/A', 1, 'VALID', 'INVALID') */
decode(status, 0, null, 1, null, 'INVALID') d_stat
-- into d_name, d_oid, d_typ, d_rem_own, d_link, d_stat
from sys.obj$
where obj# = dpnd_rec.d_id ) loop
-- dbms_output.put_line('name = ' || d_name );
--
select decode(dobj_rec.d_link,null,name,dobj_rec.d_rem_own)
into d_onam
from sys.user$
where user# = dobj_rec.d_oid;
-- dbms_output.put_line('owner= ' || d_onam );
--
if ( object_type ( dobj_rec.d_typ ) <> 'NON-EXISTENT' )
and ( d_onam <> 'SYS' ) then
dbms_output.put_line('.' || lpad(' ',dpnd_rec.level*3,' ') ||
rpad(object_type ( dobj_rec.d_typ ),12) || ' ' ||
d_onam || '.' || dobj_rec.d_name || ' ' || dobj_rec.d_link || ' ' ||
dobj_rec.d_stat );
end if;
-- exit;
end loop;
end loop;
end loop;
end loop;
end;
/

Provide input for owner as schema name and obj_name as object name ( Both in Upper Case)
Re: querying to list object dependencies [message #380526 is a reply to message #380524] Mon, 12 January 2009 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: querying to list object dependencies [message #380527 is a reply to message #380521] Mon, 12 January 2009 03:32 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rajasekhar857 wrote on Mon, 12 January 2009 10:18
IAM UNABLE TO GET IT .IT IS SHOWING NO ROWS SELECTED.AND I WANT THE RESULT HROUGH A PROCEDURE NOT THROUGH A TABLE NAME

Would you mind to post (copy-paste) your SQL*Plus session so that we could see what exactly is going on? Because, it works perfectly well in my example:
SQL> create or replace procedure my_proc
  2  is
  3  begin
  4    update dept set loc = 'Zagreb';
  5
  6    delete from emp where deptno > 50;
  7  end;
  8  /

Procedure created.

SQL> select referenced_name, referenced_type
  2  from user_dependencies
  3  where name = 'MY_PROC'
  4    and referenced_type = 'TABLE';

REFERENCED_NAME                REFERENCED_TYPE
------------------------------ -----------------
DEPT                           TABLE
EMP                            TABLE

SQL>
Re: querying to list object dependencies [message #380528 is a reply to message #380527] Mon, 12 January 2009 03:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Damn, Littlefoot. I just had my demonstration script ready. Sad

MHE
Re: querying to list object dependencies [message #380530 is a reply to message #380510] Mon, 12 January 2009 03:38 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

Here you need to specify every time dependent object type. So, again extra burden.
Re: querying to list object dependencies [message #380531 is a reply to message #380524] Mon, 12 January 2009 03:41 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can you tell me what i have to provide the value after procedure
which you put some image mark where i am unable to get it.
Re: querying to list object dependencies [message #380532 is a reply to message #380531] Mon, 12 January 2009 03:43 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

That is 8
Re: querying to list object dependencies [message #380533 is a reply to message #380524] Mon, 12 January 2009 03:47 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah iam getting some sort of information through it where it is displaying function,procedure,sequence,synonym,table.its hard to
understand,for better understanding how should i have to proceed
Re: querying to list object dependencies [message #380534 is a reply to message #380533] Mon, 12 January 2009 03:54 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

DECLARE
s_owner VARCHAR2 (30) := UPPER ('&owner');
s_name VARCHAR2 (30) := UPPER ('&object_name');

--
SUBTYPE UID IS SYS.user$.user#%TYPE;

SUBTYPE unam IS SYS.user$.NAME%TYPE;

SUBTYPE OID IS SYS.obj$.obj#%TYPE;

SUBTYPE onam IS SYS.obj$.NAME%TYPE;

SUBTYPE otyp IS SYS.obj$.type#%TYPE;

SUBTYPE ostat IS SYS.obj$.status%TYPE;

--
d_name onam;
d_typ otyp;
d_oid UID;
d_onam unam;
d_stat VARCHAR2 (30);
d_rem_own unam;
d_link VARCHAR2 (31);

--
CURSOR get_user (own_id IN UID, own_name IN unam)
IS
SELECT user# u_id, NAME u_name
FROM SYS.user$
WHERE (user# = own_id OR own_id IS NULL)
AND (NAME = own_name OR own_name IS NULL);

--
CURSOR get_obj (own_id IN UID, obj_id IN OID, obj_name IN onam)
IS
SELECT obj# o_id, NAME o_name, type# o_type,

/* decode(status, 0, 'N/A', 1, 'VALID', 'INVALID') status */
DECODE (status, 0, NULL, 1, NULL, 'VALID') status
FROM SYS.obj$
WHERE owner# = own_id
AND (obj# = obj_id OR obj_id IS NULL)
AND (NAME LIKE obj_name OR obj_name IS NULL);

--
CURSOR get_dependencies (obj_id IN OID)
IS
SELECT LEVEL, p_obj# d_id
FROM SYS.dependency$
CONNECT BY PRIOR p_obj# = d_obj#
START WITH d_obj# = obj_id;

--
FUNCTION object_type (obj_typ IN SYS.obj$.type#%TYPE)
RETURN VARCHAR2
IS
obj_desc VARCHAR2 (30) := NULL;
BEGIN
IF (obj_typ = 0)
THEN
obj_desc := 'NEXT OBJECT';
ELSIF (obj_typ = 1)
THEN
obj_desc := 'INDEX';
ELSIF (obj_typ = 2)
THEN
obj_desc := 'TABLE';
ELSIF (obj_typ = 3)
THEN
obj_desc := 'CLUSTER';
ELSIF (obj_typ = 4)
THEN
obj_desc := 'VIEW';
ELSIF (obj_typ = 5)
THEN
obj_desc := 'SYNONYM';
ELSIF (obj_typ = 6)
THEN
obj_desc := 'SEQUENCE';
ELSIF (obj_typ = 7)
THEN
obj_desc := 'PROCEDURE';
ELSIF (obj_typ = 8 )
THEN
obj_desc := 'FUNCTION';
ELSIF (obj_typ = 9)
THEN
obj_desc := 'PACKAGE';
ELSIF (obj_typ = 10)
THEN
obj_desc := 'NON-EXISTENT';
ELSIF (obj_typ = 11)
THEN
obj_desc := 'PACKAGE BODY';
ELSIF (obj_typ = 12)
THEN
obj_desc := 'TRIGGER';
ELSE
obj_desc := 'UNDEFINED';
END IF;

RETURN (obj_desc);
END;
BEGIN
FOR own_rec IN get_user (NULL, s_owner)
LOOP
FOR obj_rec IN get_obj (own_rec.u_id, NULL, s_name)
LOOP
-- dbms_output.put_line('obj# = ' || obj_rec.o_id );
DBMS_OUTPUT.put_line ( RPAD (object_type (obj_rec.o_type), 12)
|| own_rec.u_name
|| '.'
|| obj_rec.o_name
|| obj_rec.status
);

FOR dpnd_rec IN get_dependencies (obj_rec.o_id)
LOOP
--
-- dbms_output.put_line('obj# = ' || dpnd_rec.d_id );
FOR dobj_rec IN (SELECT NAME d_name, owner# d_oid, type# d_typ,
remoteowner d_rem_own,
DECODE (linkname,
NULL, NULL,
'@' || linkname
) d_link,

/* decode(status, 0, 'N/A', 1, 'VALID', 'INVALID') */
DECODE (status,
0, NULL,
1, NULL,
'INVALID'
) d_stat
-- into d_name, d_oid, d_typ, d_rem_own, d_link, d_stat
FROM SYS.obj$
WHERE obj# = dpnd_rec.d_id)
LOOP
-- dbms_output.put_line('name = ' || d_name );
--
SELECT DECODE (dobj_rec.d_link,
NULL, NAME,
dobj_rec.d_rem_own
)
INTO d_onam
FROM SYS.user$
WHERE user# = dobj_rec.d_oid;

-- dbms_output.put_line('owner= ' || d_onam );
--
IF (object_type (dobj_rec.d_typ) <> 'NON-EXISTENT')
AND (d_onam <> 'SYS')
THEN
DBMS_OUTPUT.put_line ( LPAD (' ', dpnd_rec.LEVEL * 3, ' ')
|| RPAD (object_type (dobj_rec.d_typ),
12
)
|| d_onam
|| '.'
|| dobj_rec.d_name
|| dobj_rec.d_link
|| dobj_rec.d_stat
);
END IF;
-- exit;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END;
/

Use this one instead existing
Re: querying to list object dependencies [message #380536 is a reply to message #380510] Mon, 12 January 2009 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/325168/102589/?srch=dba-village#msg_325168

Regards
Michel
Re: querying to list object dependencies [message #380538 is a reply to message #380527] Mon, 12 January 2009 04:15 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
if i want all the information i.e table,function,synonym,sequence,
procedure everything refering it then which query i have to go for
Re: querying to list object dependencies [message #380543 is a reply to message #380538] Mon, 12 January 2009 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the link I provided?

Regards
Michel
Re: querying to list object dependencies [message #380554 is a reply to message #380543] Mon, 12 January 2009 05:32 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As the time goes by, it looks as if it was yet another ./fa/3400/0/ topic.
Re: querying to list object dependencies [message #380559 is a reply to message #380554] Mon, 12 January 2009 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is the case (and actually reading all his previous posts it may be) it might be the time to ask for a ban?
But maybe he is just thickheaded.

Regards
Michel
Re: querying to list object dependencies [message #380570 is a reply to message #380559] Mon, 12 January 2009 06:21 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
to whom you are talking about?
Re: querying to list object dependencies [message #380577 is a reply to message #380570] Mon, 12 January 2009 06:42 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I am talking to the community (i.e. anyone who reads this thread), and I am talking about you (rajasekhar857). Why? Because, all I have seen is you demanding, asking, wanting something:
rajasekhar857
i want to know / I WANT TO KNOW / YEAH I WANT THE QUERY / I WANT THE RESULT / ...


At the same time, from the very beginning of this topic, you were given several pieces of code as well as link for further reading. All of that should be enough for you to produce some kind of a solution. I'm not saying that it will be perfect, but it will - at least - show that you have tried to solve the problem.

Therefore, 2 hours of useless communication lead to a conclusion that we may have a troll here. I might be wrong, of course, but - as of now, nothing suggests that.
Re: querying to list object dependencies [message #380587 is a reply to message #380577] Mon, 12 January 2009 07:36 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Littlefoot wrote on Mon, 12 January 2009 13:42

Therefore, 2 hours of useless communication lead to a conclusion that we may have a troll here. I might be wrong, of course, but - as of now, nothing suggests that.


We learn every day something new, didn't know about the troll-thing yet Wink
object dependencies [message #381034 is a reply to message #380510] Wed, 14 January 2009 23:19 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

can anyone tell me in a single query i want to know procedures/functions related object dependencies.
Re: object dependencies [message #381035 is a reply to message #381034] Wed, 14 January 2009 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
query ALL_DEPENDENCIES
Re: object dependencies [message #381037 is a reply to message #381035] Wed, 14 January 2009 23:29 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how to use that can you provide little bit information on that
Re: object dependencies [message #381039 is a reply to message #381034] Wed, 14 January 2009 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

Why do you expect us to do your work for you with NO effort from you?
Re: object dependencies [message #381041 is a reply to message #381039] Wed, 14 January 2009 23:39 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
in my schema i have given like this
select referenced_name,referenced_type from all_dependencies where
name='SEARCH'' and type='PROCEDURE';
here search is a procedure name;
the result is no rows selected
Re: object dependencies [message #381042 is a reply to message #381034] Wed, 14 January 2009 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

>here search is a procedure name;
PROVE IT!

[Updated on: Wed, 14 January 2009 23:46]

Report message to a moderator

Re: object dependencies [message #381046 is a reply to message #381042] Thu, 15 January 2009 00:00 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can i use the below coding to get the stored procedures dependencies?

SELECT lvl
, u.object_id
, u.object_type
, LPAD (' ', lvl) || object_name obj
FROM ( SELECT LEVEL lvl, object_id
FROM SYS.public_dependency s
START WITH s.object_id =
( SELECT object_id
FROM user_objects
WHERE object_name = UPPER ('&OBJECT_NAME')
AND object_type = UPPER ('&OBJECT_TYPE'))
CONNECT BY s.object_id = PRIOR referenced_object_id
GROUP BY LEVEL, object_id) tree
, user_objects u
WHERE tree.object_id = u.object_id
ORDER BY lvl
/
Re: object dependencies [message #381048 is a reply to message #381034] Thu, 15 January 2009 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>can i use the below coding to get the stored procedures dependencies?

I give up. Can you?

You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.

Re: object dependencies [message #381056 is a reply to message #381034] Thu, 15 January 2009 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted this question 3 days ago.
What in the previous topic replies do not answer your question?

Edit: It seems you REALLY are the troll Littlefoot mentionned above.

Regards
Michel

[Updated on: Thu, 15 January 2009 00:30]

Report message to a moderator

Re: object dependencies [message #381061 is a reply to message #381041] Thu, 15 January 2009 00:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajasekhar857 wrote on Thu, 15 January 2009 06:39
in my schema i have given like this
select referenced_name,referenced_type from all_dependencies where
name='SEARCH'' and type='PROCEDURE';
here search is a procedure name;
the result is no rows selected

Let me get out my crystal ball:
Is SEARCH by any chance the name of a procedure within a package, and not a stand-alone procedure?
If so, you are out of luck, you cannot trace dependencies for packaged procedures (only for the package as a whole).
Re: object dependencies [message #381081 is a reply to message #381061] Thu, 15 January 2009 03:20 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
to diplay all the object dependencies for a stored procedure in the following manner?how i have to proceed.i have followed all the
possible ways but iam mot able to get like this.


select * from ideptree;

DEPENDENCIES
---------------------------------------------------------
-----------------------
TABLE SYS.DUAL
PACKAGE BODY SYS.STANDARD
VIEW SYS.EXU7CPO
PACKAGE BODY SYS.DBMS_PCLXUTIL
PACKAGE BODY SYS.DBMS_LOCK
PACKAGE BODY SYS.DBMS_JOB
PACKAGE BODY SYS.DBMS_PRVTAQIP
PACKAGE BODY SYS.DBMS_IREFRESH
PACKAGE BODY SYS.DBMS_SNAP_INTERNAL
PACKAGE BODY SYS.DBMS_SUMREF_CHILD
PACKAGE BODY SYS.DBMS_PLUGTS
PACKAGE BODY SYS.DBMS_RCVMAN
PACKAGE BODY SYS.DBMS_BACKUP_RESTORE
PACKAGE BODY SYS.DBMS_LOGMNR_D
PACKAGE BODY SYS.DBMS_EPGC
PACKAGE BODY SYS.DBMS_HS_EXTPROC
PACKAGE BODY SYS.DBMS_HS_UTL
PACKAGE BODY CASE.JR_GEN
PACKAGE BODY CASE.JR_META
PACKAGE BODY CASE.JR_NAME
PACKAGE BODY CASE.JR_SYS_PRIVS
PACKAGE BODY CASE.JR_WORKAREA
VIEW CASE.SDD_A20
VIEW CASE.CI_NETWORK_CONNECTIONS
VIEW CASE.RM_ELEMENTS
PACKAGE BODY CASE.CIMTREE
PACKAGE BODY CASE.RMMTXI
PACKAGE BODY CASE.CIONETWORK_CONNECTION
FUNCTION CASE.PK_SDD_A20
TRIGGER CASE.SDD_A20_VAL
TRIGGER CASE.SDD_A20_D
TRIGGER CASE.SDD_E20_D
Re: object dependencies [message #381082 is a reply to message #381081] Thu, 15 January 2009 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FORMAT YOUR POST

[Updated on: Thu, 15 January 2009 03:22]

Report message to a moderator

Re: querying to list object dependencies [message #381232 is a reply to message #380524] Thu, 15 January 2009 23:14 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
thank you this is working fine what i want to kow is if i give table name i want to list the dependent procedures list.hope i will
get reply soon.also can you send the same code for sql server too.
Re: object dependencies (merged) [message #381235 is a reply to message #380510] Thu, 15 January 2009 23:39 Go to previous messageGo to previous message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
TROLL Alert!
>also can you send the same code for sql server too.

Previous Topic: How to avoid mutating error ?
Next Topic: how to get latest row if the two or more rows have the same Id
Goto Forum:
  


Current Time: Wed Dec 07 20:09:01 CST 2016

Total time taken to generate the page: 0.09180 seconds