Command rules are designed to enable the execution of user defined SQL statements in the target schema. There can be any number of Command rules applied to a schema.

Command rules are created using the New Command rule dialog box. There are two methods of operation: Script mode which runs the commands as a script or PL/SQL mode which runs the commands as a PL/SQL block. The primary difference between the two modes is the wrapper placed around the SQL Statements prior to execution. When the rule is placed in PL/SQL mode the contents are inserted into an anonymous PL/SQL block as shown below.

DECLARE
BEGIN
<Command rule contents placed here>
END;

PL/SQL mode is useful if the logic requirements are too complex for simple SQL statements. For example, very complex PL/SQL blocks using variables and loops can be written in PL/SQL mode. Command rule Script mode is simply a collection of SQL statements executed sequentially and the effect is similar to executing an SQL script in a SQL*Plus session.

It is not necessary to put a Commit statement at the end of the list of SQL statements. The Data Masker software will automatically execute an Oracle Commit once all statements execute.

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.

Creating Command Rules

Command rules are created by launching the New Command rule form using the New Rule button located on the bottom of the Rules in Set tab.

How to Create a New Command rule

Multiple SQL statements in one script

It is possible to implement an unlimited number of SQL statements in a Command rule. For example, in order to clear down some tables and re-populate them with some pre-prepared dummy data, the SQL statements below could be entered:

execute immediate 'truncate table dm_expenses';
execute immediate 'truncate table dm_expense_items';
insert into dm_expenses
   (select * from dummy_expenses);
insert into dm_expense_items
   (select * from dummy_expense_items);

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 Oracle database 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 ORA- Errors Option

Normally, the Data Masker will stop processing the rule and the masking set if the Oracle database reports any errors. These errors are reported using the normal ORA-????? error code (where ????? is a five digit number indicating the source of the error). 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 ORA-00942 error would completely halt the run of the masking set. However, in this case, the error is of no consequence. Enabling the Ignore ORA- Errors option and specifying the error code ORA-00942 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 ORA- 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.

More information on defining code for PL/SQL mode

In PL/SQL mode the SQL statements defined for a Command rule will be executed within the context of an Anonymous PL/SQL block within the target environment. For example, the SQL statements

insert into dm_expenses (select * from dummy_expenses);
insert into dm_expense_items (select * from dummy_expense_items);

would be wrapped in a PL/SQL block and executed as

DECLARE
BEGIN
insert into dm_expenses (select * from dummy_expenses);
insert into dm_expense_items (select * from dummy_expense_items);
END;

The execution of command statements within a PL/SQL block requires that they must be constructed using a format suitable for execution within PL/SQL rather than from the SQL*Plus command line. For example, if you wish to make a procedure call you would format the statement as:

My_Procedure('var1');

rather than

exec My_Procedure('var1');

If you wish to execute DDL type commands you will need to use the Oracle execute immediate package that allows the execution of dynamically generated SQL statements. For example, the statement

drop table dm_expenses;

would generate an error but would run successfully if written as

execute immediate 'drop table dm_expenses';