How I can see all tables of a specific procedure in oracle?












1















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)









share|improve this question




















  • 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
















1















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)









share|improve this question




















  • 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














1












1








1


1






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)









share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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














  • 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












1 Answer
1






active

oldest

votes


















2














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.






share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    2














    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.






    share|improve this answer




























      2














      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.






      share|improve this answer


























        2












        2








        2







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 15 '18 at 12:03









        Wernfried DomscheitWernfried Domscheit

        24.6k43061




        24.6k43061
































            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            Bressuire

            Vorschmack

            Quarantine