6 Replies Latest reply: Feb 22, 2013 6:07 PM by rp0428 RSS

    dependencies at method level

    loloFromStAlbanDeRoche
      Hello

      We'll like to know the dependencies. at the method level and not just the package level...dba_dependencies is not enough.

      IN:
      PACKAGE PAC1*

      PROCEDURE PROC1*
      SELECT TA.COL1 from TA*
      END PROC1*

      PROCEDURE PROC2*
      SELECT TB.COL1 from TB*
      END PROC1*
      END;


      OUT:

      PAC1,PROC1,TA
      PAC1,PROC2,TB

      sorry for the english :-)
        • 1. Re: dependencies at method level
          IvanBlanarik
          Hi,
          you can use the [url http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1089.htm]ALL_IDENTIFIERS view, that is available from Oracle 11g.
          • 2. Re: dependencies at method level
            loloFromStAlbanDeRoche
            I know but we are in 10g.
            • 3. Re: dependencies at method level
              IvanBlanarik
              Well ... then you need some source code parser. Maybe somebody has already written it - just try to look arround.
              This is a quickly written simplified version:
              SELECT *
              FROM
                (
                  SELECT text,
                    line                              AS procedure_begin,
                    lead(line, 1) over(order by line) AS procedure_end
                  FROM user_source
                  WHERE name = <<PACKAGE_NAME>>
                  AND type = 'PACKAGE BODY'
                  AND(lower(text) LIKE '%procedure %'
                  OR lower(text) LIKE '%function %')
                )
                pck,
                (
                  SELECT d.referenced_owner,
                    d.referenced_name,
                    s.*
                  FROM user_dependencies d,
                    (
                      SELECT text,
                        line
                      FROM user_source
                      WHERE name = <<PACKAGE_NAME>>
                      AND type = 'PACKAGE BODY'
                    )
                    s
                  WHERE name = <<PACKAGE_NAME>>
                  AND type = 'PACKAGE BODY'
                  AND referenced_owner <> 'SYS'
                  AND upper(s.text) LIKE '%'||d.referenced_name||'%'
                )
                dep
              WHERE dep.line BETWEEN pck.procedure_begin AND pck.procedure_end ;
              • 4. Re: dependencies at method level
                BluShadow
                Sounds like you want Fine Grained Dependencies information, which was introduced in 11g, but as you're on 10g you'll be out of luck.

                Whilst you can identify scope and references of PL variables/parameters etc. within code...

                http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_plscope.htm

                ... finding actual object dependencies at such a fine level you'll struggle to do in 10g (and let's hope you have no dynamic code where objects are referenced in strings)
                • 5. Re: dependencies at method level
                  loloFromStAlbanDeRoche
                  Thanks guys :-) we're going to try
                  • 6. Re: dependencies at method level
                    rp0428
                    >
                    I know but we are in 10g.
                    >
                    You only need code, not data.

                    So use a simple, free, 11g express instance and recreate your objects (no data needed). Then use PL/SCOPE to see the dependencies.