Command rules allow the execution of arbitrary SQL statements on the target database.

Command rules cannot report the number of rows on which they have operated back to the Data Masker software. Consequently the Rows Processed statistics visible on the Rule Statistics tab will always be blank for Command rules.

Example

truncate table dm_expenses;
truncate table dm_expense_items;
GO -- Oracle uses / as the block delimiter
insert into dm_expenses
    (select * from dummy_expenses);
insert into dm_expense_items
    (select * from dummy_expense_items);

Core Concepts

Run Mode 

Run mode (marked as "check for every errors in every…" in Data Masker for SQL Server) specifies how the script will be split up and sent to the target server.

Line by line ("Run as script" in Oracle)

Split the script by statements terminated by a semicolon. In the example script this would be split into 4 statements (two truncates and two inserts).

Block

Split the script into blocks. The block delimiter is different based on the target database. Oracle uses the / character, while SQL Server uses GO. In both cases these delimiters must be on a their own line. 

Data Masker will send the whole script as one if no delimiters are present.

Advanced Concepts

Multiple SQL statements in one script

It is possible to implement an unlimited number of SQL statements in a Command rule.

The only drawback to including multiple SQL statements in one rule is that if errors occur it can be harder to relate the messages returned from the SQL Server back to the SQL statement that caused the problem. If multiple 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.

The Ignore Listed Errors Option

Normally, the Data Masker will stop processing the rule and the masking set if the SQL Server reports any errors. These errors are reported using the normal SQL Server error codes and messages. Sometimes, when running Command rules, it is desirable to be able to ignore reported errors and continue processing. An example of this is a statement of the form:

drop table TempTable;

If the table does not exist, the returning 3701 Cannot drop the table error would completely halt the run of the masking set. However, in this case, the error is of no consequence. Enabling the Ignore Listed Errors option and specifying either the error code 3701 or part of the error message (or both) in the errors panel will cause those errors to be ignored for every statement in the 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 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 Command rules.