No question at this time
DBA Top 10
1 A. Kavsek 8400
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. Péran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48407
Total active users1518
Act. users last 24h0
Act. users last hour0
Registered user hits last week228
Registered user hits last month1049
Go up

To find the list of procedures and their respective tables used inside the Procedure in
Next thread: Parallel Query
Prev thread: explain plan getting full table scan.

Message Score Author Date
Hi All, Is there any possible way to find the l...... vij.. Singh May 21, 2019, 12:30
Hi, did you try using script that Michel posted...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Ales Kavsek May 21, 2019, 12:53
Hello jay, note that depending on your Oracle v...... Bruno Vroman May 21, 2019, 14:21
Hello Vijay, additional remark: I had recently...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Bruno Vroman May 21, 2019, 14:29
Like that (where dep1.sql is the script pointed by...... Michel Cadot May 21, 2019, 15:06
Indeed I did never think about it. Here's an ex...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 21, 2019, 15:19
But the dependency between PKG package body and ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot May 21, 2019, 15:33
Thanks all Now it's work. ... vij.. Singh May 22, 2019, 09:34
Thank you Michel. FYI the "core" of my PL/SQL blo...... Bruno Vroman May 22, 2019, 09:49

Follow up by mail Click here


Subject: To find the list of procedures and their respective tables used inside the Procedure in
Author: vij.. Singh, India
Date: May 21, 2019, 12:30, 89 days ago
Os info: Oracle
Oracle info: 11G
Error info: n
Message: Hi All,

Is there any possible way to find the list of stored procedures and their respective tables being used inside the Procedures?


Please help

Regards
Vijay
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: Ales Kavsek, Slovenia
Date: May 21, 2019, 12:53, 89 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

did you try using script that Michel posted? ....

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

Of course, if your stored procedures are using "execute immediate" statements, then you'll also have to search the source code for dependent objects (-> dba_source).

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: Bruno Vroman, Belgium
Date: May 21, 2019, 14:21, 89 days ago
Message: Hello jay,

note that depending on your Oracle version the script might be outdated...

And keep in mind that identifying <their respective tables> is maybe a bit "short" as you may have recursivity in dependencies, like for example:

PROCEDURE p1 uses
  - table T1
  - synonym S2
  - function F3
AND
    - synonym S2 points to view V4
    - function F3 uses table T5
AND
      - view V4 uses tables T6 and T7
(just an example, to give the idea of what can be "hidden" at deeper levels and that you might miss if you focus on "tables used")

Best regards,

Bruno Vroman.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: Bruno Vroman, Belgium
Date: May 21, 2019, 14:29, 89 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Hello Vijay,

additional remark:
I had recently to do a "similar" exercise to know the impact on other objects if a given object was dropped...

An "issue" popped up: when querying DBA_DEPENDENCIES some "links" were lost because of an "expected behaviour" or Oracle separating "packages" and "package bodies"...
So we have things like this:
  function F -> package P "end of story".
"end of story"? Well, not really, as we have also things like
  package body P -> something else.

Best regards,

Bruno
P.S. sorry for the typo in your name in first reply.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: Michel Cadot, France
Date: May 21, 2019, 15:06, 89 days ago
Message: Like that (where dep1.sql is the script pointed by Ales: ;)
SQL> create table t6 (id int, val varchar2(10));


Table created.

SQL> create table t7 (id int, val varchar2(10));

Table created.

SQL> create or replace view v4 as select t6.id, t6.val val6, t7.val val7 from t6, t7 where t7.id = t6.id;

View created.

SQL> create or replace synonym s2 for v4;

Synonym created.

SQL> create table t5 (id int, val varchar2(10));

Table created.

SQL> create or replace function f3 return number
2 is
3 res pls_integer;
4 begin
5 select count(*) into res from t5;
6 return res;
7 end;
8 /

Function created.

SQL> sho err
No errors.
SQL> create table t1 (id int, val varchar2(10));

Table created.

SQL> create or replace procedure p1 (res in out number)
2 is
3 val pls_integer;
4 begin
5 res := f3();
6 select count(*) into val from t1, s2 where s2.id = t1.id;
7 res := res + val;
8 end;
9 /

Procedure created.

SQL> sho err
No errors.
SQL> @dep1 michel p1 procedure

'michel.p1' (procedure) depends on
----------------------------------------------------------------------------------------------------
SYS.STANDARD (PACKAGE)
SYS.SYS_STUB_FOR_PURITY_ANALYSIS (PACKAGE)
MICHEL.S2 (SYNONYM)
MICHEL.V4 (VIEW)
MICHEL.T6 (TABLE)
MICHEL.T7 (TABLE)
MICHEL.F3 (FUNCTION)
SYS.STANDARD (PACKAGE)
SYS.SYS_STUB_FOR_PURITY_ANALYSIS (PACKAGE)
MICHEL.T5 (TABLE)
MICHEL.T1 (TABLE)


Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: Michel Cadot, France
Date: May 21, 2019, 15:19, 89 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
Indeed I did never think about it.
Here's an example of the failure:
SQL> create or replace procedure p (res out date) is begin res := sysdate; end;

2 /
SQL> sho err
No errors.
SQL> create or replace package pkg is function pkg_f return date; end;
2 /
SQL> create or replace package body pkg is
2 procedure pkg_p (res out date) is begin p(res); end;
3 function pkg_f return date is
4 res date;
5 begin
6 pkg_p (res);
7 return res;
8 end;
9 end;
10 /
SQL> sho err
No errors.
SQL> create or replace function f return date is begin return pkg.pkg_f(); end;
2 /
SQL> sho err
No errors.
SQL> @dep1 michel f function

'michel.f' (function) depends on
--------------------------------------------------------------------------------------------
SYS.STANDARD (PACKAGE)
SYS.SYS_STUB_FOR_PURITY_ANALYSIS (PACKAGE)
MICHEL.PKG (PACKAGE)
SYS.STANDARD (PACKAGE)


The script does not see the dependency between private package procedure "pkg_p", called by public package function pkg_f, and the stand-alone procedure "p".

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: Michel Cadot, France
Date: May 21, 2019, 15:33, 89 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
But the dependency between PKG package body and P is recorded:
SQL> @dep1 michel pkg 'package body'


'michel.pkg' (package body) depends on
---------------------------------------------------------
SYS.STANDARD (PACKAGE)
MICHEL.PKG (PACKAGE)
SYS.STANDARD (PACKAGE)
MICHEL.P (PROCEDURE)
SYS.STANDARD (PACKAGE)
SYS.SYS_STUB_FOR_PURITY_ANALYSIS (PACKAGE)


So it is possible to modify the script to handle this...

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: vij.. Singh, India
Date: May 22, 2019, 09:34, 88 days ago
Message: Thanks all
Now it's work.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: To find the list of procedures and their respective tables used inside the Procedure in
Author: Bruno Vroman, Belgium
Date: May 22, 2019, 09:49, 88 days ago
Message: Thank you Michel.
FYI the "core" of my PL/SQL block uses this to "jump" from package body to package or vice-versa:
      FOR r2 IN ( SELECT level lev

, a.owner
, a.type
, a.name
FROM dba_dependencies a
CONNECT BY NOCYCLE a.referenced_owner = PRIOR a.owner
AND a.referenced_name = PRIOR a.name
AND ( a.referenced_type = PRIOR a.type
OR a.referenced_type = 'PACKAGE'
AND PRIOR a.type = 'PACKAGE BODY'
OR a.referenced_type = 'PACKAGE BODY'
AND PRIOR a.type = 'PACKAGE'
)
START WITH a.referenced_owner = l_own
AND a.referenced_name = l_nam
AND ( a.referenced_type = l_tpe
OR a.referenced_type = 'PACKAGE'
AND l_tpe = 'PACKAGE BODY'
OR a.referenced_type = 'PACKAGE BODY'
AND l_tpe = 'PACKAGE'
)
)
Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here