Subversion Repositories DevTools

-- UsedByPackageSummary.sql
-- Determine packages that depend on a specified package-version
-- Provide the package id, name and count of package-versions
--  Match control via :MATCH
--    :MATCH 0  - Match any  package name
--    :MATCH 1  - Match Package name and Project Suffix
--    :MATCH 2  - Match exactly the package. Name, Version and Suffix
 with 
  aa AS ( select NVL(v_ext,'LINK') as ext from package_versions where pv_id = :PV_ID),
  bb AS (select pkg_id from package_versions where pv_id = :PV_ID)
select pkg.pkg_name, pkg.pkg_id, count(ppv.pv_id) AS count
  from PACKAGE_DEPENDENCIES dep,
  package_versions pv,
  package_versions ppv,
  packages pkg,
  aa, bb
  Where dep.DPV_ID = pv.pv_id
  AND dep.PV_ID = ppv.pv_id
  AND pkg.pkg_id = ppv.pkg_id
   AND (pv.PV_ID  = :PV_ID OR :MATCH != 2)
   AND (NVL(pv.v_ext,'LINK') in (  aa.ext ) OR :MATCH = 2 OR :MATCH = 0)
   AND (pv.PKG_ID in ( bb.pkg_id ) OR :MATCH = 2)
  GROUP BY pkg.pkg_name, pkg.pkg_id
  ORDER BY UPPER(pkg.pkg_name)