Install utPLSQL into one database but use from another one

[kcokco]

Dear All,
we would like to get help regarding a utPLSQL installation problem.
Asper the description the framework must be installed in a database. It is recommended to create two schemas, one for the framework and one for our own tests.

We want to install and use the utPLSQL framework by installing both the framework (UT3) and our own test codes (UT_TEST) in an independent database. And the targets of our tests are in four different schemas in two other databases.
Our question is whether this type of installation and use can cause any problems in the operation of utPLSQL.
We definitely need to install the framework in an independent database because the codes and data to be tested are overwritten every month, updated from a live database. In this case, all utPLSQL objects, codes, settings, permissions are overwritten. To avoid this, we need to transfer the entire framework and our tests to an independent database.
Are there any obstacles to the method we are planning?
We would appreciate your kind assistance.
With best regards
Cecilia

[pesse]

Hey Cecilia

I can share my (limited) opinion on your situation:

Our question is whether this type of installation and use can cause any problems in the operation of utPLSQL.

Yes, it can, and it probably will. Not because of utPLSQL per se, but because of the significant amount of introduced complexity by having to deal with db-links, permissions etc.

You say that the database under test will be overwritten every month. I would not even consider this to be a bad thing, because it means you have always acces to a "clean" database. In fact, in my own database projects, I might end up "overwriting" my development database several times a week.

What you could do is to have all the stuff you need for your tests scripted so you can install it automatically whenever you want or need it.
This approach is not a problem with the utPLSQL framework itself, since it's stateless: We actually do it every time we push changes to this repository via our CI scripts.
It will also be achievable for your test packages if you create them in a certain way: Control all the test-data you inside your test packages (for example by stored insert-scripts etc)
That way, installing all test-related stuff gets as easy as running a script.

In my projects, we use a little NodeJS tool to search all package-files that start with the UT_ prefix, collect their content into one big .sql file and run that via sqlCL or SQL*Plus whenever we want to install our test suite.

Of course, the challenges always come with the context, so feel free to share a bit about your specific context and environment.
I strongly believe that, in most cases, using utPLSQL to target a different database is a non-optimal approach, because it adds complexity and also hinders you to do stuff like TDD effectively.

Cheers,
Sam