Use Flyway to manage the schema change

Upgrade the database is challenging.  Data may be already there while you are introducing the schema change.

The base schema may be different.  New customers start from scratch with an empty database.
Existing customers may be at the different patching levels and thus require different "ALTER script" to change the schema.

Flyway is not the most advanced system I have seen and it does meet all my expectations, but it is a helpful tool to manage this schema migration problem.

Writing a SQL Parser using JavaCC

A parser can read the text, put into parts, and help you interpret the meaning. It is a tool for writing a programming language.

JavaCC is a tool I used earlier for application integration.  At that time, there is no XML and EAI vendors started emerging.  Oracle participated in the Open Application Group and helped define the standard business object document (BOD) format that can be used in exchanging data between different application vendors.


I wrote a BOD parser to take the data from the text file.  This experience later helped me in understanding XML and JSON.

COALESCE function and CASE WHEN

I tried to compare COALESCE and CASE WHEN.

COALESCE function

I have been using NVL.  However, I think that we should all move to use COALESCE.

COALESCE is more modern function that is a part of ANSI-92 standard.
NVL is Oracle specific, it was introduced in 80's before there were any standards.

This testing describes the reason: