Making an oracle deploy script independent of the environment

No longer one correct article has been written about the need and advantages of storing source codes of database schemas in version control systems (such as CVS, SVN, TFS, etc.), as well as deploying deploy - scripts.
I will not repeat, but analyze one specific aspect of this process.

It is no secret that a normally set development process consists of the actual development (Dev), internal testing (QA), acceptance testing by end users (UAT) and, directly, “Production”. Details of the life cycle may differ in individual cases, but this is not essential for the topic of the article.

Sometimes (and in the experience of the author - often) it happens that the environments at which different stages of this cycle take place may differ for one reason or another. There can be any differences. From different tablespaces, to differences in the names of schemes, DBLinks and other individual features. How to effectively solve this trouble, we consider in this article.
')




Next to the main deploy.sql script (which calls other scripts with the source code of the database objects itself), we put a daddy that stores a set of files for each environment with a set of individual parameters - define.

DEFINE DATA_TS = DEV_DATA_TS DEFINE INDEX_TS = DEV_DATA_TS DEFINE Some_Source_data_dbl = Dev_dbl_source.world -- ...  .. 

For each environment, he will be his.
Further in the source code of your base, it remains only to use variable padding, for example:

  create table my_table (sample_col VarChar(2 char) ) tablespace &DATA_TS; .... select * from dual@&Some_Source_data_dbl; 

Thus, your scripts will be indifferent to the environment, and you can more confidently say that having worked on the test environment, they will also reliably work on UAT (for example). As a result, you do not change the source codes themselves for each specific environment, thereby removing the risk of the human factor: typos, forgetfulness, the likelihood of distraction.
The risks of this, the error will get into the file of substitutional parameters itself after 1-2-3-3 iterations of the environment collection (and, as a rule, it is not that difficult to subtract the substitution file).

It remains only to correctly call all these scripts in the right order and with a minimal headache for further support. At first glance, a small question comes in: what to call these configuration files. We used to call them like this:

dev.sql
qa.sql
uat.sql
prod.sql

But now they have abandoned this practice, and we call them by the TNS-name of the medium.
What does this give? We have a .sh script of this type (I leave only the essence):

  # ...     , help,      par_file = ${3//.WORLD/} sqlplus $1/$2@$3 @deploy.sql $par_file # ...      

The script (in this simplified version, when you have only one scheme) comes to three parameters: login, password, and TNS-alias. Given that many prescribe

names.default_domain = world

then we bring everything to a uniform format (in our case: we cut off the potentially appearing ".WORLD" ). As a result, our parameter file name remains (according to the name TNSalias) which will have to be called.

It remains for the completeness of the picture to write an example of deploy.sql itself.

  --    spool _deploy.log --       --  par_file    : @./defines/&1 @../ddl/some_table_create.sql @../data/some_other_deploy_activity.sql @./validate_invalid_objects.sql @./run_post_deployment_checks.sql spool off exit 

By introducing a similar standard into your development process, over time you forget once and for all about the difference in your environments, which, believe me, greatly reduces the “headache” at the last moment - at the moment of deployment.

PS: I would like to share some skills and best-practices I have acquired over the years with databases. I would like to open a cycle of short articles based on examples with analyzes of various interesting, in my opinion, cases, tasks and pitfalls that I had to face.