I’m just now playing around with the Code Analysis feature and found some rules that are incorrectly being reported as being violated. . .
When I run Code Analysis on a trigger, I seem to always get a violation of Rule 2118: Avoid DDLs other than Truncate, Exhange/Split partition. Here is an example of a trigger that “breaks” that rule:
CREATE OR REPLACE TRIGGER ANT.TAB_INSERT_STEP_TYPES_TRG
BEFORE INSERT
ON ant.tab_insert_step_types
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
WHEN(new.ist_no IS NULL)
BEGIN
:new.ist_no := ist_seq.NEXTVAL;
END tab_insert_step_types_trg;
/
These next two rules are both raised on the same trigger. I’m including just the pertinent part of the trigger code
Rule 5914: Use untransformed column values in the WHERE clause:
In this case, the untransformed “column” it is referring to is a reference to the value in :new.columname where I am doing an UPPER
Rule 5903: All queries (dynamic and fixed) must leverage bind variables
My query is using :new.field name references and an IN parameter to the cursor. So it is using bind variables
CREATE OR REPLACE TRIGGER ANT.TAB_ZERO_ROW_WID_COLUMNS_T
BEFORE INSERT OR UPDATE
ON ANT.TAB_ZERO_ROW_WID_COLUMNS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DISABLE
DECLARE
CURSOR tab(in_column_name IN dba_tab_columns.column_name%TYPE)
IS
SELECT *
FROM dba_tab_columns
WHERE 1 = 1
AND table_name = UPPER(:new.zrw_table_name)
AND owner = UPPER(:new.zrw_owner)
AND column_name = UPPER(in_column_name);
i PLS_INTEGER := 0;
col_rec dba_tab_columns%ROWTYPE;
v_exception_message types.t_sql;
v_datatypes_selected types.t_sql;
That’s all I’ll report for now, but may have others to mention later.
Blessings,
Phyllis
–
Phyllis Helton
Data Magician
Security Gestapo
Digital Products & Strategies, Cru | Data Sciences & Analytics
Office 407-515-4452