Command rules are created using the New Command Rule form. 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 DataBee 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 DataBee software. Consequently the Rows Processed statistics visible on the Rule Statistics tab will always be blank for Command rules.
Creating Command Rules
All Rows rules are created by launching the New All Rows Rule Form using the New Extraction Rule button located on the bottom right of the main Set Designer form.
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 dtb_expenses'; execute immediate 'truncate table dtb_expense_items'; insert into dtb_expenses (select * from dummy_expenses); insert into dtb_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 DataBee will stop processing the rule and the extraction/loader 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 extraction/loader 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 dtb_expenses (select * from dummy_expenses); insert into dtb_expense_items (select * from dummy_expense_items);
would be wrapped in a PL/SQL block and executed as
DECLARE BEGIN insert into dtb_expenses (select * from dummy_expenses); insert into dtb_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:
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 dtb_expenses;
would generate an error but would run successfully if written as
execute immediate 'drop table dtb_expenses';