Improve reusability in Oracle APEX and PL/SQL projects

In most of the projects I manage, I often have to reimplement a lot of functionalities from other projects. Especially in the consulting sector, I can't switch off my knowledge. And that's not something you want. Rather, all customers unconsciously benefit from each other. Solutions that I design for one customer may be used in a similar way by another customer.

Copying code snippets is also not really good or useful here. What we are missing in the database world around Oracle is a kind of package manager similar to NPM or Maven. And due to the dependency on the database and the associated restrictions, it is also difficult to implement. Let's imagine that the use of a package requires the existence of a certain table. A table that may not be there or has yet to be created. Surely such a package could be written completely with dynamic code. The disadvantage of such an implementation would be that any errors would only appear at runtime and not during compilation.

Personally, I find it better if such a package is directly invalid on the DB and does not throw a specific error at runtime, which I then have to painstakingly debug later.

So how can you provide a package, for example?

The following approach has already proven itself in many projects. An atomic package is developed. This means that this package has no dependencies on objects that need to be brought along to install the package. Sometimes it happens that such a package has to make use of some configuration. I recommend a simple package header for this. This header only contains the variables that are customized by the developer who is responsible for the actual implementation in the target system. Here it makes sense to standardize the naming of such a Configuration-PackageHeader. I always use the suffix `_config` for this.

For a package that contains, for example, unit tests for compliance with various standards in an APEX application, there could therefore be a generic package that looks something like this:

create or replace package test_apex_application_quality is

...

end;

/



create or replace package body test_apex_application_quality is

...

  cursor cur_app_items is (select ...

                            from apex_application_items

                           where apex_application = test_apex_application_quality_config.C_APP_ID );



...

end;

/

This is just an example, but you can clearly see how the configuration package is used to restrict the items of the application to be tested.

create or replace package test_apex_application_quality_config is

  C_APP_ID constant number := 1000;

end;

/ 

This means that the 3 files could be delivered initially. If the test package is now extended to perform other tests on the application, it is sufficient to update the actual package.

Cool, is there any way to update this?

Yes, you can. With dbFlux

dbFlux is a VSCode extension and offers a command for exactly this purpose. Here it is called `Add FeatureSet as SubModule`. Such a FeatureSet is available for exactly this scenario. Simply enter the GitURL that points to such a FeatureSet. dbFlux will create / clone this repository as a Git-Submodule in the existing project folder. Such a FeatureSet must contain a manifest.json file. This file contains the information required to copy the corresponding files of the FeatureSet into the current project.
The developer of the FeatureSet can provide the files with the flag `"initial": true`. This is then only copied during initial creation and is no longer overwritten. In addition, the manifest.json file contains the version number of the FeatureSet. If this changes, dbFlux will ask whether these changes should be transferred to the current project. dbFlux will not install anything on its own, but only copy the files. These can then be installed in the development database by the developer. And with the support of dbFlow, those files will be shipped and install as usual with your project.