This discussion is archived
6 Replies Latest reply: Feb 22, 2013 4:07 PM by rp0428 RSS

dependencies at method level

loloFromStAlbanDeRoche Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I know but we are in 10g.
  • 3. Re: dependencies at method level
    IvanBlanarik Journeyer
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks guys :-) we're going to try
  • 6. Re: dependencies at method level
    rp0428 Guru
    Currently Being Moderated
    >
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points