Suggestions for working with legacy codebase with "UT" package?

[jbcooper]

We currently have a homebrew unit test system that is feeling its age, and are looking at the advances in utPLSQL and the SQL Developer extension as a replacement. One usability issue we've run into is that our codebase has a package named "UT" that's referenced extensively (for us it stands for "utility"). That obviously conflicts with running unit tests inside SQL Developer.

I saw in issue #42 that UtplsqlDao.java is now able to include the schema name. Is this a reasonable solution to include when calling the utPLSQL version of UT? Is there a better way to handle this?

[PhilippSalvisberg]

I created a conflicting object in my schema under test to reproduce the issues you have.

create table ut (dummy integer);

From that point on the extension basically stops working, because it expects that ut points to the utPLSQL package ut, typically via the public synonym created during the installation of utPLSQL.

The issue #42 you mentioned was about using the default schema ut3. For older utPLSQL versions it is necessary to access internal API for which no public synonym is created. Now the current solution determines the utPLSQL schema via the public synonym for ut. However, this is used only to access internal API, which is only necessary for utPLSQL versions less than 3.1.8.

I see the following solution approaches:

1. Use the utPLSQL schema prefix to access all utPLSQL database objects.
   However, this will also have an impact on all future test cases which require the use of the prefix as well. No example you find will work by default. All templates are also affected. I'm not really fond of this approach for various reasons. E.g. it implies that using the schema prefix is the way to go.

2. Rename your existing ut package to util or something similar.
   I can imagine that there are a lot of usages which needs to be changed. For static PL/SQL within the Oracle Database this should not be too much of a problem (because the compiler will tell you, if you forgot something, so no big risk). However, when you use this package in dynamic PL/SQL or outside of the database (e.g. in scripts) then identifying all usages is not that simple.

3. Use your ut package as proxy
   This means you copy all utPLSQL function and procedure declarations from utPLSQL's ut package specification into your ut package specification. In the body you call the utPLSQL package functions/procedures with the utPLSQL schema prefix. This is quite simple. However, by default it requires that utPLSQL is installed in any environment, also in production. If you don't want that, then you have to make sure that your ut package body compiles also in environments where no utPLSQL exists. This can be achieved either via a conditional compilation predicates or via the use of dynamic SQL. Of course you have to keep this in sync with new utPLSQL versions.

I suggest using approach 2 or 3.

[jbcooper]

Thanks for the response! I agree that approach 2 seems to be the best long-term solution, even though our ut package is likely referenced in custom stored procedures at existing customer sites. I can see renaming our core ut package to util and generating a proxy ut package as part of our build, so it's never part of a schema that runs our unit tests (not best practices by any means of course).

In the very near term I can see we'd end up going with Approach 1, just to get started using utPLSQL while we investigate the impact of changing our ut package name.

[PhilippSalvisberg]

In the very near term I can see we'd end up going with Approach 1

Just to be clear. This means that you cannot use utPLSQL for SQL Developer.