Today, I want to talk a bit about my project and the automatic rollout I implemented.
So, the topic is not MDM, the topic is automatic rollout. I hope you’ll read and comment it anyway! 
The project (customer is a German financial service provider) includes more than 400 tables (of which about 160 contains master data), about 50 APEX-forms and a couple of packages, procedures, functions, jobs, etc.
Our database is an ORACLE 11g with APEX 4.1, the operating system is unfortunately Windows.
My mission was to build an automatic rollout by pushing only one button.
I devided every table-script into 6 autonomous scripts:
1. Table-definition with comments
2. Sequence
3. Trigger
4. Indices
5. Constraints
6. Foreign-Keys
I’ve thrown all table-definitions in a separate folder, all sequences in a separate folger, all trigger in a separate… . I’ve thrown all packages in a separate folder, all jobs …., etc. I have added a number to all the names of the package-scripts in order to keep the correct order of the import.
Now, I’ve written some nice batchjobs (for every folder one single batchjob), which take al look at the folder and call a SQL-Plus for every single entry. Around this I implemented another batchscript which calls the batchjobs for the folders. So I have to write the database parameters only one time, I deliver them to every sub-script automatically. There is one main reason for fragmentation the table-scripts. With a fragmentation I am able to import first all tables, then all sequences, after that all triggers, … . And I don’t have to care about the order within the folders.
No fragmentation would have meant that I had to be careful about the implementation order of the tables. Now, I don’t care about the order! And – if I want to import a new table, I only have to throw the parts of the scripts in special folders.
Okay, the real world is a little bit more complicated.
I devided it a second time in “master data” and “dynamic data”. The reason for that? We have to import the “master data” as well. I decided to do that via SQL-Loader and a separated csv-file (As you know in a new folder!) for every master-data-table.
Inserting the master data means that I have to care about the order of inserting. What a mess! I decided to keep the correct order with numbering the ctl-files (in a new folder) consecutively and write a batchjob for the folder of ctl-files. Another way would have been to install the tables without any constraints, insert all data and afterwords enable the constraints again. In this way, I would have to search in a more complicated way for errors. So I decided to go the other way. I belived, that this was the easier way. I belive that was the correct decision.
For ervery single action, I am going to write a log-file in a log-folder in order to see what happens. The last action is again a separate batchjob. I’ve written a batchjob which searches in all log-files for some keywords like ‘ERROR’, ‘ORA-’ or ‘WARNING’ and writes the filename and the line with the finding in a new file. So, at the end, I only have to look at the last one log-file to see how much rubbish I produced.
That was only the very very simplified version of my automatic rollout (and I didn’t tell you about the APEX-rollout). I’m gonna try to create a presentation in which everything will be shown a little bit more detailed and clearer. If I’ll only find some time…. .
What do you think about this proceeding? Do you have some better ideas or some good advices for me? Please tell me!
