Command rules are created using the New Command Rule dialog box. 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';

It is possible to implement multiple SQL statements in a Command Rule - up to a maximum of 1024 characters. 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);

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

The Ignore ORA- Errors Option

Normally, the Data Masker will stop processing the set of masking rules 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:

execute immediate 'drop table XXTempTab';

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 itself is of no consequence. Enabling the Ignore ORA- Errors Option will allow subsequent rules to be processed.

It is important to realize that the Ignoring ORA Errors works on a per-rule basis and the handling is internal to the Data Masker software not the Oracle database. In other words, if a rule contains multiple statements then any SQL statements listed after a statement that fails will not be processed by the Oracle database. If you enable this option make sure that each Command Rule contains only one SQL statement.