KS DB Merge Tools

Documentation

KS DB Merge Tools for Oracle

Scripting

Scripting Support

KS DB Merge Tools for Oracle supports its own scripting language that can be used from the command line and has a lot of features provided by the Standard version GUI. Command line tool is available for Windows and Linux operating systems. Using on Windows requires a Standard license, and a Linux version is free to use, except for automated use by non-individuals.

Here is the script example which copies all data from HR DB to the blank database with the same structure:

LogTo(
    fileName: 'c:\temp\myLog.txt',
    ifExists: Append,
    addTimestamp: true,
    bufferSize: 100);

Set $db1 = DbOpen(connectionString: 'Data Source=mydbhost:1521/HR;User ID=myuser;Password=mypsw;');
Set $db2 = DbOpen(connectionString: 'Data Source=mydbhost:1521/HRBLANK;User ID=myuser;Password=mypsw;');

/* DependencyLevel sorting: process tables without foreign keys first, than tables dependant on processed tables and so on:*/

ForEach $table in GetCommonTables(
    nameRegex: '',
    commonPrimaryKey: true,
    sortBy: DependencyLevel,
    sortOrder: Asc,
    sortByDependencyLevelDb: $db1)

    DataMerge(
        changeType: New,
        fromDb: $db1,
        toDb: $db2,
        fromTable: $table,
        identityInsert: True,
        withTransaction: True,
        onError: StopScript);

Save such kind of text to text file with .ksdms extension and use the following command to run this script:

"c:\Program Files (x86)\KS DB Merge Tools for Oracle\OracleMergeCmd.exe" myscript.ksdms

To verify script syntax without execution, you can use /checkOnly switch:

"c:\Program Files (x86)\KS DB Merge Tools for Oracle\OracleMergeCmd.exe" myscript.ksdms /checkOnly

It will parse the script, validate its syntax and report to console output if it has any errors. You can also use the Script Editor application to prepare scipts: it has syntax highlight, allows to check and run scipts and shows the exit code and console output of OracleMergeCmd.exe:

For many typical diff & merge tasks script can be generated automatically using the 'Generate automation script stub' toolbar button. Just click that button and application will generate a draft script for the current tab and open Script Editor application with that script. You will have to do few adjustments (like specify log file path) and script is ready for further testing and execution.

Installation

On Windows command line and Script Editor applications are installed to the application folder which is specified when you run .msi application installation package.

On Linux you need to download .tar.gz file, extract to some folder and allow execution. Here is the example of these steps for ~/bin/ksdbmergetools/for-oracle installation folder:

mkdir -p ~/bin/ksdbmergetools/for-oracle
wget -qO- https://ksdbmerge.tools/oracle/OracleMerge_Linux_x64_1.7.0.tar.gz | tar xvz -C ~/bin/ksdbmergetools/for-oracle
chmod u+x ~/bin/ksdbmergetools/for-oracle/OracleMergeCmd

Command Line Arguments

Application supports both /windows and --linux styles to define command line parameters. Parameters listed in the tables below are case-insensitive.

Parameter	Description
/? --? /help --help	Show help, it is also shown if no or invalid parameters specified
/checkonly --checkonly -c	Validate script syntax without execution and print found errors
/forcescriptrun --forcescriptrun -f	Force execution of data or objects merge/delete functions, if they were used without specifying fileName parameter to save generated script. If fileName is missing and this parameter is not specified, then script execution is stopped with exit code 10 ('Deny script run' error)
/accepteula --accepteula	When running on Linux, suppress request to confirm the End-User License Agreement, can be applied only if you have read and accepted terms from file EULA.txt, located in the application folder. When running on Windows, this parameter is ignored because EULA is accepted during installation.

Scripting Language

Here are the most common rules of this scripting language:

Script consists of list of statements, each statement must be terminated with semicolon (';')
There are 3 types of statements:

Function call that does performs some action (BatchDataDiff(), BatchObjectsDiff(), DataDiff(), DataDelete(), DataMerge(), dbExec(), LoadDiffProfile(), LogTo(), LogPrint(), ObjectsDiff(), ObjectsDelete(), ObjectsMerge())
Assign to a variable some tabular data:
```
Set $selObjects =
| Name             |
| dbo.vContactInfo |
| dbo.vSalesPerson |
;
```
or result of function call (Set $db = DbOpen())
ForEach operator that iterates a variable thru result of function that returns a list (ForEach $table in GetCommonTables() /*Do some action - DataMerge() or DataDelete()*/;), note that semicolon is required only after ForEach body action, not after GetCommonTables()

Each function has number of parameters, some parameters are mandatory and some optional, but parameter names are mandatory (LogPrint(text:'OK') is good, but LogPrint('OK') is the error)
Each function parameter has specified type, it can be:
- String - strings are single-quoted
- Boolean - True or False
- Number - positive integer number
- Custom value from specified list, applicable only for given parameter (like Append value for ifExists parameter of LogTo())
- Database variable - result or DbOpen() function call
- String variable - result or GetCommonTables() function call
- Table variable - result or table assignment
Script supports block comments in C-style (/*here is some comment*/)
Some functions and their arguments may have synonym names, for example DbOpen and OpenDatabase - it is the same function. That's because of historical reasons: originally it was introduced as OpenDatabase, but then was renamed to DbOpen to be more consistent with other method names. Original name is left for the backward compatibility.
Most of the functions are related to some concrete application tabs, for example BatchObjectsDiff allows to produce the same export as on the Home tab.

Limitations

Here are some application features which are currently can be done only using GUI but not supported by scripting for now:

Data - diff/merge/delete only selected rows
Data - merge only selected columns
Data - Id-Remap Merge
Data - transaction rollback for merge/delete on combination of enabled withTransaction and non-Proceed onError
Data - detailed diff of particular column value
Objects - detailed export and partial merge of table definitions and view definitions, functionality provided in GUI by Table structure diff and View definition diff tabs
Objects - object definition text diff (functionality provided in GUI by Text diff tab)
Objects - find (functionality provided in GUI by Find tab)

Return Codes

0: Success
1: Standard license validation error when running on Windows, run GUI mode to check
2: Denied to run on Windows for the Free license
3: Invalid command line arguments
4: Script parsing errors
5: Log initialization failure
6: Script interrupted by onError: StopScript parameter
7: Statement not supported. The scripting language is common for all KS DB Merge Tools and it may happen that some statement is implemented for some particular tool but not for another.
8: Invalid object name used in script
9: EULA is not accepted when running on Linux
10: Deny script run. Script contains merge/delete statement for immediate execution (without specifying fileName parameter to save script) and script is executed without /forcescriptrun command line argument
255: Script interrupted by any other unknown error (DB connection failure, etc)

Functions Reference

All by name	Schema	Data	Other
BatchDataDiff BatchObjectsDiff DataDelete DataDiff DataMerge DbExec DbOpen GetCommonTables LoadDiffProfile LogPrint LogTo ObjectsDelete ObjectsDiff ObjectsMerge	BatchObjectsDiff ObjectsDelete ObjectsDiff ObjectsMerge	BatchDataDiff DataDelete DataDiff DataMerge	DbExec DbOpen GetCommonTables LoadDiffProfile LogPrint LogTo

BatchDataDiff

Generate a data diff summary report, similar to the Export in the Batch data diff tab.

Parameter	Type	Optional	Default value	Description
changeType	Custom	Yes	All	Specify which tables needs to be exported: All - all tables, regardless of their Total/New/Changed count values New - only tables having new rows Changed - only tables having changed rows or those which have different Total value NewAndChanged - union of New and Changed rows
calcType	Custom	Yes	Total	Specify which counts are calculated: Total - only Total counts TotalAndNew - only Total and New counts All - calculate all counts
showTables	Boolean	Yes	True ^(*)	Include regular tables
showViews	Boolean	Yes	False ^(*)	Include views
showCustomMappings	Boolean	Yes	False ^(*)	Include custom mappings from the diff profile which was loaded previously with LoadDiffProfile(). Mapping should be marked as 'Show in Batch data diff'
showCustomQueries	Boolean	Yes	False ^(*)	Include custom queries from the diff profile which was loaded previously with LoadDiffProfile(). Queries should be marked as 'Show in Batch data diff'
dataSlice	String	Yes	True	Open data slice from from the diff profile which was loaded previously with LoadDiffProfile().
fileName	String	No		Report file name, with .xlsx or .json file extension

^(*) These defaults are valid for non-data-slice mode. If data slice is specified, then default is True if data slice has corresponding objects, similar to behavior of the Batch data diff tab.

BatchObjectsDiff

Generate a schema/objects diff summary report, similar to the Export in the Home tab.

Parameter	Type	Optional	Default value	Description
changeType	Custom	No		Specify which object types needs to be exported: All - all object types, regardless of their Total/New/Changed count values New - only object types having new objects Changed - only object types having changed objects NewAndChanged - union of New and Changed rows Unchanged - only object types having unchanged objects
fileName	String	No		Report file name, with .xlsx or .json file extension

DataDelete (Synonym: DeleteData)

Delete data from the given table of the given database. You can specify second database to compare and delete only new/changed/both rows.

Parameter	Type	Optional	Default value	Description
changeType	Custom	No	All	Specify which rows needs to be deleted: All - all table rows New - only rows that are new comparing to another database Changed - rows that exist in both DBs but have different values NewAndChanged - union of New and Changed rows
fromDb	Database variable	No		Database to be processed
table (synonym: fromTable)	String or variable	No		Table needs to be processed, either string with table name or table variable returned from GetCommonTables() function call
comparedToDb	Database variable	Yes		Database to compare and identify New/Changed/NewAndChanged rows
onError	Custom	Yes	Proceed	Error processing logic: Proceed - skip failed row and proceed to process the rest StopCommand - stop current DataDelete execution and don't process rest of rows StopScript - stop the whole script execution
withTransaction	Boolean	Yes	False	Process all rows as a single transaction. If onError is not Proceed, then changes will be applied as all or nothing. Using transactions can also improve performance
fileName	String	Yes		Save generated SQL script to the specified .sql file (instead of execution)
ifExists	Custom	Yes	Append	Behavior if fileName already exists. StopScript - stop script execution Overwrite - overwrite existing file Append - append new sql statements at the end of existing file

DataDiff

Generate a data diff report for the given table, view, custom mapping or custom query; similar to the Export in the Data diff or Query result diff tabs.

Parameter	Type	Optional	Default value	Description
tableType	Custom	Yes	Table	Table object type: Table - regular table View - view CustomMapping - custom mapping from the diff profile which was loaded previously with LoadDiffProfile() CustomQuery - custom query from the diff profile which was loaded previously with LoadDiffProfile()
table	String or variable	No		Table that needs to be processed, either string with table name or table variable returned from GetCommonTables() function call
changeType	Custom	Yes	All	Specify which rows needs to be exported: All - all table rows New - only rows that are new comparing to another database Changed - rows that exist in both DBs but have different values NewAndChanged - union of New and Changed rows Unchanged - rows that exist in both DBs and have no different values
limit	Number	Yes	0	Max number of rows to put into file, specify 0 to include all rows
fileName	String	No		Report file name, with .xlsx or .json file extension

DataMerge (Synonym: MergeData)

Merge data of the given table from one database to another

Parameter	Type	Optional	Default value	Description
changeType	Custom	Yes		Specify which rows needs to be merged: New - only rows existing in source and missing in target Changed - rows that exist in both DBs but have different values NewAndChanged - union of New and Changed rows
fromDb	Database variable	No		Source database
table (synonym: fromTable)	String or variable	No		Table needs to be processed, either string with table name or table variable returned from GetCommonTables() function call
toDb	Database variable	No		Target database
identityInsert	Boolean	Yes	True	If target table ID column is identity/autoincrement: True - import ID values from the source database, False - ignore source ID values, generate new ID values for imported rows. Note that in this case processed rows will keep to be considered as New by application because they will have different IDs
onError	Custom	Yes	Proceed	Error processing logic: Proceed - skip failed row and proceed to process the rest StopCommand - stop current MergeData execution and don't process rest of rows StopScript - stop the whole script execution
withTransaction	Boolean	Yes	False	Process all rows as a single transaction. If onError is not Proceed, then changes will be applied as all or nothing. Using transactions can also improve performance
fileName	String	Yes		Save generated SQL script to the specified .sql file (instead of execution)
ifExists	Custom	Yes	Append	Behavior if fileName already exists. StopScript - stop script execution Overwrite - overwrite existing file Append - append new sql statements at the end of existing file

DbExec

Run SQL statement on the specified database.

Parameter	Type	Optional	Default value	Description
db	Database variable	No		Target database
sql	String	No		SQL statement to run

DbOpen (Synonym: OpenDatabase)

Specify database connection and return its reference as a variable. Connection string specified in format that is used by Oracle Data Provider for .NET, see https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/ConnectionConnectionString.html

Parameter	Type	Optional	Default value	Description
connectionString	String	No		Connection string text

GetCommonTables

Returns a list of table names that exists in both databases. This list can be used only inside the ForEach statement, see example above.

Parameter	Type	Optional	Default value	Description
nameRegex	String	Yes		Regex filter by table name
sortBy	Custom	Yes	Name	Specify by which table attribute we need to sort result: Name - by table name DelendencyLevel - by table dependency level. See Table DL for more information.
sortByDependencyLevelDb	Database variable	See description		Each database has its own foreign keys and dependency level of the same tables can be different. If we do sorting by DependencyLevel - this parameter becomes mandatory. Otherwise it is optional
sortOrder	Custom	Yes	Asc	Specify sort order: Asc - ascending Desc - descending
commonPrimaryKey	Boolean	Yes		If True - return only tables which have the same primary key definition in both databases, if False - only different. If not specified - return all tables

LoadDiffProfile

Load specified diff profile to process its items (custom mappings, queries and data slices). 'Load on start' diff profile option is not applied for OracleMergeCmd. Necessary diff profile needs to be loaded in the script using this LoadDiffProfile() function.

Parameter	Type	Optional	Default value	Description
fileName	String	No		Diff profile file name

LogPrint

Add text to the log

Parameter	Type	Optional	Default value	Description
text	String	No		Text of log entry

LogTo

Setup logging settings

Parameter	Type	Optional	Default value	Description
fileName	String	No		Log file name
ifExists	Custom	Yes	Append	Behavior if fileName already exists. StopScript - stop script execution Overwrite - overwrite existing file Append - append new log entries at the end of existing file
addTimestamp	Boolean	Yes	True	Specify timestamp of log entries
logDataResult	Boolean	Yes	True	Add an entry on each processed row like 'Merging [DatabaseLogID] = 1.. Done.'
logDataScript	Boolean	Yes	False	Add entry of each executed INSERT/UPDATE/DELETE statement
logConnectionString	Boolean	Yes	False	During database open, add to the log database connection string (as is, including passwords if any)
bufferSize	Number	Yes	0	Write log entries in batch mode, can improve performance, but some log entries can be lost in case of abnormal program termination

ObjectsDelete

Delete objects from the given database.

Parameter	Type	Optional	Default value	Description
objectType	String	No		Object type, specified exactly the same as on the Home tab
changeType	Custom	Yes	All	Specify which objects needs to be deleted: All - all objects New - only objects that are new comparing to another database Changed - objects that exist in both DBs but have different definition NewAndChanged - union of New and Changed objects Unchanged - objects that exist in both DBs and have same definition
fromDb	Database variable	No		Database to be processed
selectObjects	Table variable	Yes		Single-column table with list of object names
onError	Custom	Yes	Proceed	Error processing logic: Proceed - skip failed object and proceed to process the rest StopCommand - stop current ObjectsDelete execution and don't process rest of objects StopScript - stop the whole script execution
fileName	String	Yes		Save generated SQL script to the specified .sql file (instead of execution)
ifExists	Custom	Yes	Append	Behavior if fileName already exists. StopScript - stop script execution Overwrite - overwrite existing file Append - append new sql statements at the end of existing file

ObjectsDiff

Generate an objects/schema diff report for the given object types; similar to the Export in the Object list tab.

Parameter	Type	Optional	Default value	Description
objectType	String	No		Object type, specified exactly the same as on the Home tab
changeType	Custom	Yes	All	Specify which objects needs to be exported: All - all objects New - only objects that are new comparing to another database Changed - objects that exist in both DBs but have different definition NewAndChanged - union of New and Changed objects Unchanged - objects that exist in both DBs and have same definition
selectObjects	Table variable	Yes		Single-column table with list of object names
fileName	String	No		Report file name, with .xlsx or .json file extension

ObjectsMerge

Merge data of the given table from one database to another

Parameter	Type	Optional	Default value	Description
objectType	String	No		Object type, specified exactly the same as on the Home tab
changeType	Custom	Yes	All	Specify which objects needs to be merged: New - only objects that are new comparing to another database Changed - objects that exist in both DBs but have different definition NewAndChanged - union of New and Changed objects
fromDb	Database variable	No		Source database
toDb	Database variable	No		Target database
selectObjects	Table variable	Yes		Single-column table with list of object names
onError	Custom	Yes	Proceed	Error processing logic: Proceed - skip failed object and proceed to process the rest StopCommand - stop current ObjectsMerge execution and don't process rest of objects StopScript - stop the whole script execution
fileName	String	Yes		Save generated SQL script to the specified .sql file (instead of execution)
ifExists	Custom	Yes	Append	Behavior if fileName already exists. StopScript - stop script execution Overwrite - overwrite existing file Append - append new sql statements at the end of existing file

Last updated: 2024-11-21