| Line 2... |
Line 2... |
| 2 |
-- :RTAG_ID
|
2 |
-- :RTAG_ID
|
| 3 |
-- :PV_ID
|
3 |
-- :PV_ID
|
| 4 |
--
|
4 |
--
|
| 5 |
-- All the package-vesrions that use a specified package-version in a specified release
|
5 |
-- All the package-vesrions that use a specified package-version in a specified release
|
| 6 |
--
|
6 |
--
|
| 7 |
With small_pd as ( select * from package_dependencies where pv_id in (select pv_id from release_content where rtag_id = :RTAG_ID )),
|
7 |
With small_pd as ( SELECT pd.*, pd.pkg_id || pv1.v_ext as pkg_sig, pd.dpkg_id || pv2.v_ext as dpkg_sig FROM package_dependencies pd, package_versions pv1, package_versions pv2 WHERE pv1.pv_id IN ( SELECT pv_id FROM release_content WHERE rtag_id = :RTAG_ID) AND pd.pv_id = pv1.pv_id AND pd.dpv_id = pv2.pv_id ),
|
| 8 |
aa as (select * from (
|
8 |
aa as (select * from (
|
| 9 |
select LEVEL as ll, pv_id from small_pd pd connect by prior pv_id = dpv_id start with dpv_id=:PV_ID
|
9 |
select LEVEL as ll, pv_id from small_pd pd connect by prior pkg_sig = dpkg_sig start with dpv_id=:PV_ID
|
| 10 |
UNION
|
10 |
UNION
|
| 11 |
SELECT 0 AS ll, to_number(:PV_ID) AS pv_id FROM dual
|
11 |
SELECT 0 AS ll, to_number(:PV_ID) AS pv_id FROM dual
|
| 12 |
) )
|
12 |
) )
|
| 13 |
select t.*, p.pkg_name, pv.pkg_version, pv.MODIFIED_STAMP,pv.DLOCKED, rc.deprecated_state, pv.build_time, rc.PKG_STATE, usr.FULL_NAME, usr.USER_EMAIL
|
13 |
select t.*, p.pkg_name, pv.pkg_version, pv.MODIFIED_STAMP,pv.DLOCKED,pv.pkg_id, rc.deprecated_state, pv.build_time, rc.PKG_STATE, usr.FULL_NAME, usr.USER_EMAIL
|
| 14 |
from (select MIN(ll) as buildLevel, pv_id
|
14 |
from (select MIN(ll) as buildLevel, pv_id
|
| 15 |
from aa group by pv_id
|
15 |
from aa group by pv_id
|
| 16 |
) t, packages p, package_versions pv, release_content rc, USERS usr
|
16 |
) t, packages p, package_versions pv, release_content rc, USERS usr
|
| 17 |
where t.pv_id = pv.pv_id and pv.pkg_id = p.pkg_id AND rc.rtag_id = :RTAG_ID and rc.pv_id = pv.pv_id AND pv.MODIFIER_ID = usr.USER_ID
|
17 |
where t.pv_id = pv.pv_id and pv.pkg_id = p.pkg_id AND rc.rtag_id(+) = :RTAG_ID and rc.pv_id(+) = pv.pv_id AND pv.MODIFIER_ID = usr.USER_ID
|
| 18 |
order by buildLevel, upper(pkg_name)
|
18 |
order by buildLevel, upper(pkg_name)
|
| - |
|
19 |
|