The following rules in Code Analysis are not triggering correctly.
2118 - Avoid DDLs other than Truncate, Exchange/Split partition.
2120 - Avoid SELECT statements with more than two UNION operations
2129 - For a given parenthetical level, avoid more than six OR operations in WHERE clauses
2832 - Avoid hard-coded constraints on VARCHAR2 variables by anchoring the declaration to a variable or column with %TYPE, or by defining a SUBTYPE and then declaring the variable based on that subtype
4213 - Use CASE statement rather than a complex conditional IF statement.
4804 - Avoid use of EXIT/RETURN statements in a FOR/WHILE loop
4809 - A loop that contains DML statements should be refactored to use BULK COLLECT and FORALL
4812 - SELECT BULK COLLECT INTO (implicit bulk query) could cause problems with session memory if too many rows are retrieved. Consider using FETCH BULK COLLECT with LIMIT clause.
5002 - Avoid assigning strings of zero length to character-type variables.
5406 - Remove unused parameter or modify code to use the parameter.
5825 - Consider replacing INSERT/UPDATE or UPDATE/INSERT pairs working on the same table by a MERGE
5917 - Do not use 'Insert into ... select * ...' statements
6811 - Avoid parameters or variables with embedded spaces
Combination of product defect and user misinterpretion on how the rules should work.
WORKAROUND
The workaround is to edit the rules (only possible in Expert Edition) by going to View | Toad Options | Code Analysis | General. On the right-hand-side under the Code Analysis Rules section, click on Edit Rules. Then enter the correct XPath Expression as follows (see screen shot SOL93311_Fig_1.jpg):
Fixed expression for 5917
//INSERT/descendant::EXPR[@cat="query"] [SELECT_LIST/SELECT_ITEM/QNAME/IDENTIFIER [last()] [@value="*"] ]
Fixed expression for 2832
//TYPE/SIZE [../QNAME/@value = "varchar2"] [not(ancestor::TYPE[@cat="subtype"])]
Fixed expression for 4809:
//INTO_LIST [../../(EXPR[@cat="query"], EXECUTE_IMMEDIATE, FETCH) ]
[ancestor::LOOP[1] [not(@cat="forall")] [.//(INSERT, DELETE, UPDATE, MERGE)] ]
[not (@bulk_collect="true")],
//(INSERT, DELETE, UPDATE, MERGE)/.[ancestor::LOOP[1][not(@cat = "forall")]]/TOKEN[1]
Development have merged rule 4811 into 4809 (so 4811 will be removed). In addition, a FETCH / BULK COLLECT without LIMIT will also be flagged because it can lead to same memory problems. Also will not flag SELECT if no DML statements are found.
4811 would just disappear because it doesn’t make sense to test on a sequence of FETCH followed by DML. It could be the opposite in case of the following code:
FETCH
LOOP
INSERT/UPDATE
FETCH
END LOOP
Fixed expression for 5825
//BLOCK//INSERT [INSERT_INTO/TABLE_REF_LIST/TABLE_REF/QNAME/@value = ..//UPDATE/TABLE_REF_LIST/TABLE_REF/QNAME/@value] /TOKEN[1],
//BLOCK//UPDATE [TABLE_REF_LIST/TABLE_REF/QNAME/@value = ..//INSERT//INSERT_INTO/TABLE_REF_LIST/TABLE_REF/QNAME/@value] /TOKEN[1]
This rule cannot be fully coded with XPath alone. It requires an additional data flow analysis (inside the parser) for optimum correctness. The work on data flow analysis is on-going but no date of completion can be provided. We'll try to make sure rule 5825 a part of it as soon as it becomes production.
Fixed expression for 4812
//EXPR[@cat="query"]/INTO_LIST [@bulk_collect="true"],
//FETCH[not(BULK_LIMIT)]/INTO_LIST [@bulk_collect="true"]
Fixed expression for 2129
//WHERE/EXPR[@cat="or"][1]/
EXPR[@cat="or"][1]/
EXPR[@cat="or"][1]/
EXPR[@cat="or"][1]/
EXPR[@cat="or"][1]/
EXPR[@cat="or"][1]/
EXPR[@cat="or"][1]
Fixed expression for 6811
//VAR_DECL/QNAME/IDENTIFIER[@value[contains(.,' ')]],
//PARAM_SPEC/QNAME/IDENTIFIER[@value[contains(.,' ')]]
By design, it only flags the declarations now, not the implementations.
Rule 2120 is checking for union, union_all, intersect, and minus.
Rule 4804 was requested specifically by a customer, this rule can turn off.
Fixed expression for 5406
//PARAM_SPEC_LIST/PARAM_SPEC/QNAME/@value [ not(. = (../../../..//(ASSIGN/*, EXPR, PARAM)/QNAME/@value))]
Fixed expression for 2118
//CHUNK[starts-with(@type, "Alter")
or starts-with(@type, "Create")
or starts-with(@type, "Drop")
or ends-with(@type, "Statistics")
or starts-with(@type, "Purge")
or starts-with(@type, "Flashback")
or starts-with(@type, "Set")
or starts-with(@type, "Truncate")
or @type=("Audit", "Noaudit", "Rename")]
[not((descendant::SPLIT_PARTITION, descendant::EXCHANGE_PARTITION, descendant::TRUNCATE_PARTITION,
descendant::PROCEDURE_BODY, descendant::FUNCTION_BODY, descendant::PACKAGE_BODY))]
/descendant::QNAME[1]
Fixed expression for 4213
//IF[count(ELSIF) > 1][ELSE]/TOKEN[1]
Fixed expression for 5002
//(RHS, DEFAULT)/LITERAL[@type="text" and @value="''"]
STATUS
Waiting for a fix in a future release of Toad for Oracle.
Expert edition is required to edit rules. Professional edition only allows for execution of Code Analysis. Those on Professional edition can create a new rule and enter the correct XPath Expression.
Not all the rules currently have XPath expressions. We are in the process of converting them all to XPath.
Not all rules should be used based on the conditions of what they're trying to accomplish. In 11.6, user will be able to ignore rules but until that is released, user should create a rule set without that rule if that rule doesn't apply to their conditions.
More on the request for Rule 4804
We want such occurrences flagged simply because we've seen too many abuses with respect to UNION operations. The flag will serve as a cue to review the code carefully. The most common mistake is where the two halves of a UNION can be easily merged into a single SELECT statement with a few strategically-placed CASE expressions.
