Skip to content

Latest commit

 

History

History
57 lines (51 loc) · 2.45 KB

ExecuteImmediateWarning.md

File metadata and controls

57 lines (51 loc) · 2.45 KB

ExecuteImmediateWarning

Warning on use of EXECUTE IMMEDIATE statements.

regex: [\t\n\r\s]*(?i:execute)[\t\n\r\s]*(?i:immediate\s)

Sample Failing Scripts

--changeset amalik:execute_immediate
DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
      USING dept_id;
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

Sample Error Message

CHANGELOG CHECKS
----------------
Checks completed validation of the changelog and found the following issues:

Check Name:         Check for specific patterns in sql (ExecuteImmediateWarning)
Changeset ID:       execute_immediate
Changeset Filepath: changeLogs/2_objects/02_storedprocedure/execute_immediate.sql
Check Severity:     INFO (Return code: 0)
Message:            Warning! EXECUTE IMMEDIATE statements found.

Step-by-Step

Prompt Command or User Input
> liquibase checks customize --check-name=SqlUserDefinedPatternCheck
Give your check a short name for easier identification (up to 64 alpha-numeric characters only) [SqlUserDefinedPatternCheck1]: ExecuteImmediateWarning
Set the Severity to return a code of 0-4 when triggered. (options: 'INFO'=0, 'MINOR'=1, 'MAJOR'=2, 'CRITICAL'=3, 'BLOCKER'=4)? [INFO]: <Choose a value: 0, 1, 2, 3, 4>
Set 'SEARCH_STRING' (options: a string, or a valid regular expression): [\t\n\r\s]*(?i:execute)[\t\n\r\s]*(?i:immediate\s)
Set 'MESSAGE' [A match for regular expression <SEARCH_STRING> was detected in Changeset .]: Warning! EXECUTE IMMEDIATE statements found.
Set 'STRIP_COMMENTS' (options: true, false) [true]: true