How I can see all tables of a specific procedure in oracle?
example
Package procedure Table
pkg1 proc1 tab1 (table used in proc1)
pkg1 proc1 tab2 (table used in proc1)
pkg1 proc2 tab3 (table used in proc2)
pkg1 proc2 tab4 (table used in proc2)
oracle
add a comment |
example
Package procedure Table
pkg1 proc1 tab1 (table used in proc1)
pkg1 proc1 tab2 (table used in proc1)
pkg1 proc2 tab3 (table used in proc2)
pkg1 proc2 tab4 (table used in proc2)
oracle
2
A procedure inside a package? This is almost impossible. While Oracle stores dependencies, it can only tell you which tables are referenced from the package (except even for tables used in dynamic SQL). It cannot tell you which procedure within. You'd have to parse the package code yourself, which would be tough to say the least.
– Thorsten Kettner
Nov 15 '18 at 12:13
Thx for the response.
– davide rho
Nov 15 '18 at 14:03
add a comment |
example
Package procedure Table
pkg1 proc1 tab1 (table used in proc1)
pkg1 proc1 tab2 (table used in proc1)
pkg1 proc2 tab3 (table used in proc2)
pkg1 proc2 tab4 (table used in proc2)
oracle
example
Package procedure Table
pkg1 proc1 tab1 (table used in proc1)
pkg1 proc1 tab2 (table used in proc1)
pkg1 proc2 tab3 (table used in proc2)
pkg1 proc2 tab4 (table used in proc2)
oracle
oracle
edited Nov 15 '18 at 11:53
a_horse_with_no_name
302k46460555
302k46460555
asked Nov 15 '18 at 11:51
davide rhodavide rho
61
61
2
A procedure inside a package? This is almost impossible. While Oracle stores dependencies, it can only tell you which tables are referenced from the package (except even for tables used in dynamic SQL). It cannot tell you which procedure within. You'd have to parse the package code yourself, which would be tough to say the least.
– Thorsten Kettner
Nov 15 '18 at 12:13
Thx for the response.
– davide rho
Nov 15 '18 at 14:03
add a comment |
2
A procedure inside a package? This is almost impossible. While Oracle stores dependencies, it can only tell you which tables are referenced from the package (except even for tables used in dynamic SQL). It cannot tell you which procedure within. You'd have to parse the package code yourself, which would be tough to say the least.
– Thorsten Kettner
Nov 15 '18 at 12:13
Thx for the response.
– davide rho
Nov 15 '18 at 14:03
2
2
A procedure inside a package? This is almost impossible. While Oracle stores dependencies, it can only tell you which tables are referenced from the package (except even for tables used in dynamic SQL). It cannot tell you which procedure within. You'd have to parse the package code yourself, which would be tough to say the least.
– Thorsten Kettner
Nov 15 '18 at 12:13
A procedure inside a package? This is almost impossible. While Oracle stores dependencies, it can only tell you which tables are referenced from the package (except even for tables used in dynamic SQL). It cannot tell you which procedure within. You'd have to parse the package code yourself, which would be tough to say the least.
– Thorsten Kettner
Nov 15 '18 at 12:13
Thx for the response.
– davide rho
Nov 15 '18 at 14:03
Thx for the response.
– davide rho
Nov 15 '18 at 14:03
add a comment |
1 Answer
1
active
oldest
votes
Use this one:
SELECT *
FROM USER_DEPENDENCIES
WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY')
AND REFERENCED_TYPE = 'TABLE';
However, of course it does not cover dynamic SQL statements.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53318886%2fhow-i-can-see-all-tables-of-a-specific-procedure-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use this one:
SELECT *
FROM USER_DEPENDENCIES
WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY')
AND REFERENCED_TYPE = 'TABLE';
However, of course it does not cover dynamic SQL statements.
add a comment |
Use this one:
SELECT *
FROM USER_DEPENDENCIES
WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY')
AND REFERENCED_TYPE = 'TABLE';
However, of course it does not cover dynamic SQL statements.
add a comment |
Use this one:
SELECT *
FROM USER_DEPENDENCIES
WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY')
AND REFERENCED_TYPE = 'TABLE';
However, of course it does not cover dynamic SQL statements.
Use this one:
SELECT *
FROM USER_DEPENDENCIES
WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY')
AND REFERENCED_TYPE = 'TABLE';
However, of course it does not cover dynamic SQL statements.
answered Nov 15 '18 at 12:03
Wernfried DomscheitWernfried Domscheit
24.6k43061
24.6k43061
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53318886%2fhow-i-can-see-all-tables-of-a-specific-procedure-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
A procedure inside a package? This is almost impossible. While Oracle stores dependencies, it can only tell you which tables are referenced from the package (except even for tables used in dynamic SQL). It cannot tell you which procedure within. You'd have to parse the package code yourself, which would be tough to say the least.
– Thorsten Kettner
Nov 15 '18 at 12:13
Thx for the response.
– davide rho
Nov 15 '18 at 14:03