Cross Database Command rules allow running arbitrary SQL on any SQL Server or Oracle database. The Cross Database Login Panel shows how to specify connections to other databases. The target server needn't be included in any rule controller of the masking set.

Core Concepts

Target Database

The database that the SQL command will be run against. See the Cross Database Login Panel for full details of how to specify a cross database connection.

SQL Command

The SQL command to execute on the remote database. This must be written in target database's SQL dialect.

Multiple SQL statements can be included. They will be split into statements terminated by a semicolon.

-- an example of multiple SQL Statements designed to execute on a remote Oracle database.
drop table DM_STAGING_TABLE1;
create table DM_STAGING_TABLE1 (IDCol number(10), DataCol varchar2(60));

In the above example the Cross Database Command rule will ensure a new empty temporary table is created and a subsequent Table Mover rule could be used to copy rows from the SQL Server database over to the Oracle instance for later synchronization by other rules. It should be noted that if synchronization is the desired result this procedure could also be implemented in one step using a dedicated Cross Database Table-to-Table rule as well

Advanced Concepts

Error Management and Handling

Normally, the Data Masker will stop processing the rule and the masking set if the remote database reports any errors. These errors are reported using the typical error codes and messages appropriate to the remote database type. These will not necessarily be the same as SQL Server error codes and messages if the remote database type is something other than an SQL Server database.

Sometimes, when running Cross Database Command rules, it is desirable to be able to ignore reported errors and continue processing. An example of this is an SQL statement of the form:

drop table TempTable;

If the table does not exist, an Oracle database will return ORA-00942 table or view does not exist as an error which would completely halt the run of the masking set. However, in this case, since the rule is attempting to drop a table which already does not exist, the error is of no consequence. Enabling the Ignore Listed Errors option on the Error Mgr. tab and specifying either the error code ORA-00942 or part of the error message (or both) in the errors panel will cause those errors to be ignored for every statement in the Cross Database Command rule and allow subsequent statements to be processed.

It is important to realize that the Ignore Listed Errors option applies to all SQL statements in the Cross Database Command rule. If it is not appropriate to ignore the specified error code for all statements in the rule then it is advisable to split the statements out into separate Cross Database Command rules.

One drawback to including multiple SQL statements in one Cross Database Command Rule is that if errors occur, it can be harder to relate the messages returned from the remote database back to the SQL statement that caused the problem. In such cases it may be preferable to implement multiple Cross Database Command rules with one statement per rule. If multiple Cross Database Command rules are used to implement a series of statements, remember to implement rule blocks or dependencies in order to control the exact sequence in which the commands execute.