7 Replies Latest reply: Dec 5, 2012 2:25 AM by 712870 RSS

    How to Find Packages/Procedures using any particular table

    586006
      Hi,

      I want to find out the procedures,packages using any particular table. Please tell me how to find out.
        • 1. Re: How to Find Packages/Procedures using any particular table
          Vivek L
          583003 wrote:
          Hi,

          I want to find out the procedures,packages using any particular table. Please tell me how to find out.
          SQL> desc all_dependencies;
           Name                                      Null?    Type
           ----------------------------------------- -------- ----------------------------
           OWNER                                     NOT NULL VARCHAR2(30)
           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)
           DEPENDENCY_TYPE                                    VARCHAR2(4)
          http://docs.oracle.com/cd/E14072_01/server.112/e10820/statviews_1067.htm
          • 2. Re: How to Find Packages/Procedures using any particular table
            Manik
            Check this with your tablename: (provided you have access to dba_dependencies view)
            SELECT *
              FROM dba_dependencies
             WHERE name = 'EMP'
            UNION ALL
            SELECT *
              FROM dba_dependencies
             WHERE referenced_name = 'EMP'
            Cheers,
            Manik.
            • 3. Re: How to Find Packages/Procedures using any particular table
              rp0428
              >
              I want to find out the procedures,packages using any particular table. Please tell me how to find out.
              >
              You can use the utldtree.sql in the rdbms\admin folder to do that. See my reply in this thread
              Re: Finding out all procedures , functions in a tree like structure in sequence

              The comments in the script (and in the thread) show how to use it. The script creates a table, a view and a procedure.
              Then you just use it like the comments describe. Here is the result for my SCOTT.EMP table. You can see I have a lot of packages and functions and things that depend on that table.
              execute deptree_fill('table', 'scott', 'emp');
              
              select * from ideptree
              
              OBJECT_NAME,SCHEMA,NAME,TYPE,STATUS
              0. EMP,SCOTT,EMP,TABLE,VALID
                    1. PAK_TEST1,SCOTT,PAK_TEST1,PACKAGE,INVALID
                          2. PAK_TEST1,SCOTT,PAK_TEST1,PACKAGE BODY,VALID
                    1. GET_EMP,SCOTT,GET_EMP,FUNCTION,INVALID
                    1. THREE_TABLE_EMP_VIEW,SCOTT,THREE_TABLE_EMP_VIEW,VIEW,INVALID
                    1. TEST_PKG,SCOTT,TEST_PKG,PACKAGE,INVALID
                          2. TEST_PKG,SCOTT,TEST_PKG,PACKAGE BODY,VALID
                    1. TEST_F,SCOTT,TEST_F,FUNCTION,INVALID
                    1. PIPELINE,SCOTT,PIPELINE,FUNCTION,INVALID
                    1. GETOLDSAL,SCOTT,GETOLDSAL,FUNCTION,INVALID
                    1. UPDATESAL_RETURNOLDSAL,SCOTT,UPDATESAL_RETURNOLDSAL,FUNCTION,INVALID
                    1. EMP_HIER_PKG,SCOTT,EMP_HIER_PKG,PACKAGE,INVALID
                          2. EMP_HIER_PKG,SCOTT,EMP_HIER_PKG,PACKAGE BODY,INVALID
                    1. EMP_TBL,SCOTT,EMP_TBL,TYPE,INVALID
                    1. EMP_HIER_PKG,SCOTT,EMP_HIER_PKG,PACKAGE BODY,INVALID
                    1. TEST_REFCURSOR_PKG,SCOTT,TEST_REFCURSOR_PKG,PACKAGE BODY,VALID
                    1. DOIT,SCOTT,DOIT,PROCEDURE,VALID
                    1. DOIT1,SCOTT,DOIT1,PROCEDURE,VALID
                    1. LSZRPT,SCOTT,LSZRPT,PACKAGE,INVALID
                          2. LSZRPT,SCOTT,LSZRPT,PACKAGE BODY,INVALID
                    1. F_GET_NAME,SCOTT,F_GET_NAME,FUNCTION,INVALID
                    1. TEST_REFCURSOR_PKG1,SCOTT,TEST_REFCURSOR_PKG1,PACKAGE BODY,INVALID
                    1. TEST2,SCOTT,TEST2,PROCEDURE,INVALID
                    1. TAX_COST_SP,SCOTT,TAX_COST_SP,PROCEDURE,INVALID
                    1. V_EMP,SCOTT,V_EMP,VIEW,VALID
                          2. EMPLOYEES,USER1,EMPLOYEES,SYNONYM,VALID
                    1. VIEW_EMP_TEST,SCOTT,VIEW_EMP_TEST,VIEW,VALID
                    1. MASTER_MV,SCOTT,MASTER_MV,MATERIALIZED VIEW,INVALID
                    1. TEST_VIEW,HR,TEST_VIEW,VIEW,VALID
                    1. MYEMPVIEW,SCOTT,MYEMPVIEW,VIEW,VALID
                    1. MYEMPVIEW1,SCOTT,MYEMPVIEW1,VIEW,VALID
                    1. TEST_NDX,SCOTT,TEST_NDX,VIEW,VALID
                    1. PKG1,SCOTT,PKG1,PACKAGE,VALID
                    1. EMP_ACTIONS,SCOTT,EMP_ACTIONS,PACKAGE BODY,INVALID
                    1. MYPACKAGE,SCOTT,MYPACKAGE,PACKAGE,VALID
                    1. V_EMP1,SCOTT,V_EMP1,VIEW,VALID
                    1. IDEXISTS,SCOTT,IDEXISTS,PROCEDURE,VALID
                    1. VIEW_TEST_EMP,SCOTT,VIEW_TEST_EMP,VIEW,INVALID
                    1. GET_EMP_REC,SCOTT,GET_EMP_REC,FUNCTION,VALID
                    1. GET_EMP_CLOB,SCOTT,GET_EMP_CLOB,FUNCTION,VALID
              • 6. Re: How to Find Packages/Procedures using any particular table
                712870
                Hi everyone,
                Did anyone interest in the topic search for dependencies between objects in the database?
                I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does an object use in any PL/SQL code.
                Often do you have a need to get answers to these questions?

                I say about an object usage presentation, not about its relations only.
                Do you want to know, how opbject used?
                If it's a table - what's DML?(insert/select/...)
                If it used in a package - which procedure of package uses in?
                The object usage in an external objects - Oracle*Forms/Reports,Flat/Word files, Informatica.

                I'll want to know, if everybody interested to one.
                • 7. Re: How to Find Packages/Procedures using any particular table
                  jeneesh
                  Digging two months old thread?