APEX Deployment Tool - Export database objects

E

Exporting database objects is probably the most important thing. Same as for the APEX exports, the more often you do it, the better position you will have. And I store everything in the repo, every single object in database has its own clone in file. I have worked for a company where they stored only changed objects in the repo, imagine that!

Every time I am done with a task or at the end of the day I export all changed objects in just few seconds.

Update 5/2024: If you want to know the OPY approach, check the Database versioning with data, APEX apps and migrations article.

This is part of multiple ADT articles:

• Introduction & workflow
• Setup connections, config, folder structure
• Recompile invalid objects
• Searching APEX and searching repo for objects
• Export APEX & live upload
• Export database objects (this article)
• Automated patching & deployment

The fastest way how to get your changes is to export just the objects which changed in past few days. There are more filters available, you can ask for specific object types, names or any combination.

cd ~/Documents/YOUR_PROJECT_REPO/; adt export_db
cd ~/Documents/YOUR_PROJECT_REPO/; adt export_db -recent 2
cd ~/Documents/YOUR_PROJECT_REPO/; adt export_db -recent 2 -verbose
cd ~/Documents/YOUR_PROJECT_REPO/; adt export_db -recent 2 -type VIEW -name ABC%

This is how the export looks like in a verbose mode:

Objects will be exported into a folder structure you can configure in config.yaml file. The structure itself should be intuitive enough. Each object type has its own dedicated folder. Each object is represented by a `.sql` file (if you require `.pkb` and `.pks`, that is not supported yet).

There is a big difference in between other tools and ADT. I like to have the exports clean, so I can see changes instantly and I don't have to deal with clutter. On the left side there is the "pretty" export from DBMS_METADATA, on the right side is ADT:

Similar transformations happen to all supported objects. For PL/SQL code there is just transformation for tabs and removal of trailing spaces, for jobs there is completely customized process and a very different output. At the end the goal is the same, to have as readable code as possible with minimal noise/clutter.

If you have a lot of objects or if you are a subfolder lover, you can rearrange your files into subfolders (views/ABC, views/DEF... or even views/A/B/C if that make any sense to you). Just move exported files into subfolders you like and ADT will respect this structure. If you have a multiple schemas in one repo, you can have that too. You can customize the path for schemas in config.yaml file. For example you can go with:

path_objects : '{$INFO_SCHEMA}/database/'
path_objects : 'database/{$INFO_SCHEMA}/'
path_objects : 'database_{$INFO_SCHEMA}/'
path_objects : 'database/'
path_apex    : ^same as above

In connections.yaml file you can specific which objects names (comma delimited SQL patterns like: `APP%,ABC%`) will be exported or ignored. So if you have a mess in your schema, you don't have to put this mess into the repo.

DEV:
    schemas:
        APPS:
            export:
                ignore      : ''
                prefix      : 'APP%'

It also export made and received grants, user roles, directories. So if grants changes, you an see that in the repo changes. And these grants are automatically used in the patches if you are running APEX in dedicated schema.

Please let me know if you are missing something or something is not clear.