Migration of 2.3.1 example does not work in windows 11g XE HR Schema, 0 items processed

[tkleiber]

Hi!

Despite I have run the example before, so an entry is listed in ut_package, the migration package does not process anything.

See attached sql script and outputs from sql developer.

utplsql_migrate_2_to_3.sql.txt utplsql_migrate_2_to_3.dbms.output.txt utplsql_migrate_2_to_3.script.output.txt

Kind regards Torsten

[jgebal]

Hi @tkleiber, Thanks for detailed report. Let us review it and get back to you.

[just-doit]

Hi,

I think the SELECT statement in ut_v2_migration.migrate_v2_packages is not correct. In my case it returns no rows. After changing it to

SELECT p.owner,
       p.prefix || p.name AS name,
       p.description AS package_desc,
       NVL(p.prefix, c.prefix) prefix,
       s.name suite_name,
       s.description AS suite_desc,
       o.status
  FROM utp.ut_package p, utp.ut_suite s, utp.ut_config c, all_objects o
 WHERE     p.id IN (  SELECT MAX(p2.id) KEEP (DENSE_RANK FIRST ORDER BY p2.suite_id DESC NULLS LAST)
                        FROM utp.ut_package p2
                    GROUP BY UPPER(p2.owner), UPPER(p2.name))
       AND p.suite_id = s.id(+)
       AND p.owner = c.username(+)
       AND p.owner = o.owner
       AND p.prefix || p.name = o.object_name
       AND o.object_type IN ('PACKAGE')
       AND p.owner = NVL(:a_owner_name, p.owner)
       AND p.name = NVL(:a_package_name, p.name)
       AND (   s.name = :a_suite_name
            OR :a_suite_name IS NULL)

it worked.