Upload Process

Data upload process compares the data included in the blueprint file (generated by the extract process) and that extracted from a target database and generates output SQL to synchronize them.

The java utility OraSdUpg in OraDBI.jar, included in this package, is used by the process utility to compare and synchronize the data in the target database with that in the input blueprint file.

OraSdUpg reads an input parameter file for the list of the tables to be upgraded along with the selection criteria and upgrade rules for each table.

Each table has a corresponding record in the file with following 6 fields separated by semi-colon:

Following is the example of how these records should look like in the file:

CI_​LOOKUP;1;LANGUAGE_​CD = 'ENG';T;T;F;T;DESCR

A sample file upload_​cmsystbls.lst has been included in this package. Implementer can make a copy of this file and edit it to match their requirements.

Before making connection to the target database, the utility reads the header from the blueprint and sets NLS_​LANG environment variable on the client machine. It then validates this character set setting to the character set of the target db after making a connection and warns user if there is a mismatch.

The utility can be executed in verification and modification modes. In verification modes, the action SQL statements are simply written to the log file but in modification mode they are applied the target database.

It is very important to note that the primary requirement for OraSdUpg is definition (column and primary key) of tables being upgrade in the target database should be same as that in the database from which the binary file was extracted.

Be careful while selecting the table and the selection criteria because to compare the data, the utility, for each table, first loads the data from the binary file and the database in the memory. If a table has huge amount of data and selection criteria set causes the utility to work on large quantity of data, it may run out of memory.

To avert unique key constraint violation error that can be caused by improper sequence of data deletion and insertion on a table and also the foreign key issues, the utility first gathers all the generated action statements for all the tables before executing them. The execution of all the generated statements is done in multiple iterations. After each iteration, all the failed statements during that iteration are collected and executed again in the next iteration. The iterations are repeated till either all the statements in iteration are executed successfully or they fail.

The utility disables and enables all the triggers on the tables being upgraded before and after applying database changes. No triggers get executed during the system data upgrade.

OraSdUpg accepts the following parameters:

Note: It is recommend that the implementers execute the upload process first in the verification mode and review the SQL before running the tool in the modification mode.