| Line 1... |
Line 1... |
| 1 |
-- UsedByPackageSummary.sql
|
1 |
-- UsedByPackageSummary.sql
|
| 2 |
-- Determine packages that depend on a specified package-version
|
2 |
-- Determine packages that depend on a specified package-version
|
| 3 |
-- Provide the package id, name and count of package-versions
|
3 |
-- Provide the package id, name and count of package-versions
|
| - |
|
4 |
-- Match control via :MATCH
|
| - |
|
5 |
-- :MATCH 0 - Match any package name
|
| - |
|
6 |
-- :MATCH 1 - Match Package name and Project Suffix
|
| - |
|
7 |
-- :MATCH 2 - Match exactly the package. Name, Version and Suffix
|
| 4 |
select pkg.pkg_name, pkg.pkg_id, count(ppv.pv_id) AS count
|
8 |
select pkg.pkg_name, pkg.pkg_id, count(ppv.pv_id) AS count
|
| 5 |
from PACKAGE_DEPENDENCIES dep,
|
9 |
from PACKAGE_DEPENDENCIES dep,
|
| 6 |
package_versions pv,
|
10 |
package_versions pv,
|
| 7 |
package_versions ppv,
|
11 |
package_versions ppv,
|
| 8 |
packages pkg
|
12 |
packages pkg
|
| 9 |
Where dep.DPV_ID = pv.pv_id
|
13 |
Where dep.DPV_ID = pv.pv_id
|
| 10 |
AND dep.PV_ID = ppv.pv_id
|
14 |
AND dep.PV_ID = ppv.pv_id
|
| 11 |
AND pv.PKG_ID in ( select pkg_id from package_versions where pv_id = :PV_ID )
|
- |
|
| 12 |
AND NVL(pv.v_ext,'LINK') in ( select NVL(v_ext,'LINK') from package_versions where pv_id = :PV_ID )
|
- |
|
| 13 |
AND pkg.pkg_id = ppv.pkg_id
|
15 |
AND pkg.pkg_id = ppv.pkg_id
|
| 14 |
AND (pv.pv_id = :PV_ID OR :MATCH = 0)
|
16 |
AND (pv.PV_ID = :PV_ID OR :MATCH != 2)
|
| - |
|
17 |
AND (NVL(pv.v_ext,'LINK') in ( select NVL(v_ext,'LINK') from package_versions where pv_id = :PV_ID ) OR :MATCH = 2 OR :MATCH = 0)
|
| - |
|
18 |
AND (pv.PKG_ID in ( select pkg_id from package_versions where pv_id = :PV_ID) OR :MATCH = 2)
|
| 15 |
GROUP BY pkg.pkg_name, pkg.pkg_id
|
19 |
GROUP BY pkg.pkg_name, pkg.pkg_id
|
| 16 |
ORDER BY UPPER(pkg.pkg_name)
|
20 |
ORDER BY UPPER(pkg.pkg_name)
|
| 17 |
|
- |
|
| 18 |
|
- |
|