> ## Documentation Index
> Fetch the complete documentation index at: https://docs.codeant.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Plsql

<AccordionGroup>
  <Accordion title="Jump statements should not be redundant">
    <div class="paragraph">
      <p>Jump statements, such as <code>RETURN and CONTINUE</code> let you change the default flow of program execution, but jump statements that direct the control flow to the original direction are just a waste of keystrokes.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE PROCEDURE print_numbers AS
      BEGIN
      FOR i in 1..4 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
      CONTINUE; -- Noncompliant
      END LOOP;
      RETURN;     -- Noncompliant
      END;
      ```

      ```plsql Fix theme={null}
      CREATE PROCEDURE print_numbers AS
      BEGIN
      FOR i in 1..4 LOOP
      DBMS_OUTPUT.PUT_LINE(i);
      END LOOP;
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Predefined exceptions should not be overridden">
    <div class="paragraph">
      <p>Naming custom exceptions the same as predefined ones, while technically acceptable, is not a good practice.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      no_data_found EXCEPTION; -- Noncompliant, overrides an Oracle predefined exception

      d VARCHAR2(1);
      BEGIN
      SELECT dummy INTO d FROM DUAL WHERE dummy = 'Y'; -- Will raise STANDARD.NO_DATA_FOUND
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No data found!'); -- Won't be executed, as NO_DATA_FOUND was overriden, confusing!
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unknown error!'); -- *Will* be executed
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      my_own_exception EXCEPTION; -- Compliant

      d VARCHAR2(1);
      BEGIN
      SELECT dummy INTO d FROM DUAL WHERE dummy = 'Y'; 
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No data found!'); -- *Will* be executed
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unknown error!');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Lines in a multiline comment should start with *">
    <div class="paragraph">
      <p>Multi-line comments are more readable when each line is aligned using the "\*" character. At most one violation is created for each comment</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      /*
      this line is not aligned and ugly Non-Compliant
      no violation is created on this line, even though is it also bad
      */

      /* this is Compliant */
      ```

      ```plsql Fix theme={null}
      /*
      * this is much better Compliant
      */

      /* this is Compliant */
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FETCH ... BULK COLLECT INTO should be used">
    <div class="paragraph">
      <p>The <code>FETCH ... INTO statement is inefficient when used in a loop (where many records are expected). It leads to many context-switches between the SQL and PL/SQL engines. Instead, the FETCH ... BULK COLLECT INTO</code> statement will issue the SQL requests in bulk, minimizing context switches.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE largeTable AS SELECT ROWNUM AS id FROM all_objects;

      SET TIMING ON
      DECLARE
      x PLS_INTEGER;
      CURSOR largeCursor IS SELECT ROWNUM FROM largeTable;
      largeTableRowId BINARY_INTEGER;
      BEGIN
      OPEN largeCursor;

      x := 0;
      LOOP
      FETCH largeCursor INTO largeTableRowId; -- Noncompliant
      EXIT WHEN largeCursor%NOTFOUND;

      x := x + largeTableRowId;
      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Sum of rownums using alternative 1: ' || x);

      CLOSE largeCursor;
      END;
      /
      SET TIMING OFF

      DECLARE
      r largeTable%ROWTYPE;
      CURSOR myCursor IS SELECT * FROM largeTable;
      BEGIN
      OPEN myCursor;
      FETCH myCursor INTO r; -- Compliant, outside of a loop
      CLOSE myCursor;
      END;
      /

      DROP TABLE largeTable;
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE largeTable AS SELECT ROWNUM AS id FROM all_objects;

      SET TIMING ON
      DECLARE
      x PLS_INTEGER;
      CURSOR largeCursor IS SELECT * FROM largeTable;
      TYPE largeTableRowIdArrayType IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
      largeTableRowIdArray largeTableRowIdArrayType;
      BEGIN
      OPEN largeCursor;

      x := 0;
      LOOP
      FETCH largeCursor BULK COLLECT INTO largeTableRowIdArray LIMIT 1000; -- Compliant

      FOR i IN largeTableRowIdArray.FIRST .. largeTableRowIdArray.LAST LOOP
        x := x + largeTableRowIdArray(i);
      END LOOP;

      EXIT WHEN largeCursor%NOTFOUND;
      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Sum of rownums using alternative 2: ' || x);

      CLOSE largeCursor;
      END;
      /
      SET TIMING OFF

      DECLARE
      r largeTable%ROWTYPE;
      CURSOR myCursor IS SELECT * FROM largeTable;
      BEGIN
      OPEN myCursor;
      FETCH myCursor INTO r; -- Compliant, outside of a loop
      CLOSE myCursor;
      END;
      /

      DROP TABLE largeTable;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="CASE should be used rather than DECODE">
    <div class="paragraph">
      <p><code>DECODE is an old function that has been replaced by the easier to understand and more common CASE. Unlike DECODE, CASE</code> may also be used directly within PL/SQL.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      operand CHAR(1) := 'B';
      l_result PLS_INTEGER;
      BEGIN
      -- Noncompliant
      SELECT DECODE(operand, 'A', 1
                         , 'B', 2
                         , 'C', 3
                         , 'D', 4
                         , 'E', 5
                         , 'F', 6
                         , 7)
      INTO l_result
      FROM dual;

      DBMS_OUTPUT.PUT_LINE('l_result = ' || l_result); -- 2
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      operand CHAR(1) := 'B';
      l_result PLS_INTEGER;
      BEGIN

      l_result := CASE operand
                  WHEN 'A' THEN 1
                  WHEN 'B' THEN 2
                  WHEN 'C' THEN 3
                  WHEN 'D' THEN 4
                  WHEN 'E' THEN 5
                  WHEN 'F' THEN 6
                  ELSE 7
                END;

      DBMS_OUTPUT.PUT_LINE('l_result = ' || l_result); -- 2
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Column aliases should be defined using AS">
    <div class="paragraph">
      <p>For better readability, column aliases should be used with the <code>AS</code> keyword. If it is missing, it could be misread as another column being selected.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      result DUAL.dummy%TYPE;
      BEGIN
      SELECT
      dummy d -- Non-Compliant - could be misread as selecting both "dummy" and a column "d"
      INTO
      result
      FROM
      DUAL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      result DUAL.dummy%TYPE;
      BEGIN
      SELECT
      dummy AS d -- Compliant
      INTO
      result
      FROM
      DUAL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Procedures and functions should be encapsulated in packages">
    <div class="paragraph">
      <p>Having a bunch of standalone functions or procedures reduces maintainability because it becomes harder to find them and to see how they are related. Instead, they should be logically grouped into meaningful packages.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE PROCEDURE show_name(name VARCHAR2) AS -- Non-Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Name: ' || name);
      END;
      /

      DROP PROCEDURE show_name;
      ```

      ```plsql Fix theme={null}
      CREATE PACKAGE employee AS
      PROCEDURE show_name;
      END;
      /

      CREATE PACKAGE BODY employee AS
      name VARCHAR2(42);

      PROCEDURE show_name AS  -- Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Name: ' || name);
      END;
      END;
      /

      DROP PACKAGE employee;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FUNCTIONS should not have OUT parameters">
    <div class="paragraph">
      <p>Functions with <code>OUT parameters are complex to understand. Indeed, it is impossible to tell, just by looking at the function call, whether an argument is a input or output. Moreover, functions with OUT</code> parameters cannot be called from SQL. It is better to either break such functions up into smaller ones, which each return a single value, or to return several values at once, by combining them in a collection, record, type, or table row.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE employee(
      firstName VARCHAR2(42),
      name VARCHAR2(42),
      phone VARCHAR2(42)
      );

      INSERT INTO employee VALUES ('John', 'Smith', '+1');

      DECLARE
      firstName VARCHAR2(42);
      name VARCHAR2(42);
      phone VARCHAR2(42);

      -- This DOES NOT return the employee name
      FUNCTION getEmployeeInfos(firstName OUT VARCHAR2, phone OUT VARCHAR2) RETURN VARCHAR2 AS -- Non-Compliant, confusing
      name VARCHAR2(42);
      BEGIN
      SELECT firstName, name, phone INTO firstName, name, phone FROM employee;
      RETURN name;
      END;
      BEGIN
      name := getEmployeeInfos(firstName, phone);

      DBMS_OUTPUT.PUT_LINE('firstName: ' || firstName);
      DBMS_OUTPUT.PUT_LINE('name: ' || name);
      DBMS_OUTPUT.PUT_LINE('phone: ' || phone);
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE employee(
      firstName VARCHAR2(42),
      name VARCHAR2(42),
      phone VARCHAR2(42)
      );

      INSERT INTO employee VALUES ('John', 'Smith', '+1');

      DECLARE
      emp employee%ROWTYPE;

      FUNCTION getEmployeeInfos RETURN employee%ROWTYPE AS -- Compliant
      emp employee%ROWTYPE;
      BEGIN
      SELECT * INTO emp FROM employee;
      RETURN emp;
      END;
      BEGIN
      emp := getEmployeeInfos;

      DBMS_OUTPUT.PUT_LINE('firstName: ' || emp.firstName);
      DBMS_OUTPUT.PUT_LINE('name: ' || emp.name);
      DBMS_OUTPUT.PUT_LINE('phone: ' || emp.phone);
      END;
      /

      DROP TABLE employee;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Nested loops should be labeled">
    <div class="paragraph">
      <p>Labeled loops are useful, especially when the code is badly indented, to match the begin and end of each loop. When loops are nested, labeling them can improve the code’s readability. This rule detects nested loops which do not have a start label.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      LOOP
      LOOP -- Noncompliant, this nested loop is not labeled
        EXIT;
      END LOOP;

      EXIT;
      END LOOP;

      FOR i IN 1..10  LOOP
      WHILE true LOOP -- Noncompliant, this nested loop has no start label
        EXIT;
      END LOOP nestedLoopLabel1;

      EXIT;
      END LOOP;

      WHILE true LOOP
      <<nestedLoopLabel2>>
      LOOP -- Compliant, but better with an end label
        EXIT;
      END LOOP;

      EXIT;
      END LOOP;
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      LOOP
      <<nestedLoopLabel0>>
      LOOP
        EXIT;
      END LOOP nestedLoopLabel0;

      EXIT;
      END LOOP;

      FOR i IN 1..10  LOOP
      <<nestedLoopLabel1>>
      WHILE true LOOP
        EXIT;
      END LOOP nestedLoopLabel1;

      EXIT;
      END LOOP;

      WHILE true LOOP
      <<nestedLoopLabel2>>
      LOOP
        EXIT;
      END LOOP nestedLoopLabel2;

      EXIT;
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FORMS_DDL(COMMIT) and FORMS_DDL(ROLLBACK) should not be used">
    <div class="paragraph">
      <p><code>FORMS\_DDL</code> command, like every DDL statements, is performing an implicit COMMIT. It should be used only if there is no pending transaction otherwise this transaction is automatically committed without updating the Form statuses. Also, the potentially acquired locks are lost in case of this implicit COMMIT.</p>
    </div>

    <div class="paragraph">
      <p>"FORMS\_DDL('COMMIT')" and "FORMS\_DDL('ROLLBACK')" should be used with care and most of the time, "COMMIT\_FORM" or "ROLLBACK\_FORM" should be preferred.</p>
    </div>

    <div class="paragraph">
      <p>Check the Oracle Forms documentation for more details.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      FORMS_DDL('COMMIT');
      ```

      ```plsql Fix theme={null}
      COMMIT_FORM;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Block start and end labels should match">
    <div class="paragraph">
      <p>Labeled blocks are useful, especially when the code is badly indented, to match the begin and end of each block. This rule verifies that block start and end labels match, when both are specified.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      NULL;
      END; -- Compliant, no labels at all
      /

      <<myBlockLabel1>>
      BEGIN
      NULL;
      END; -- Compliant, only starting label
      /

      BEGIN
      NULL;
      END myBlockLabel2; -- Compliant, only ending label
      /

      <<myBlockLabel3>>
      BEGIN
      NULL;
      END myBlockLabel4; -- Noncompliant, labels mismatch
      /

      <<myBlockLabel6>>
      <<myBlockLabel6>>
      BEGIN
      NULL;
      END myBlockLabel6; -- Noncompliant, several starting labels
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      NULL;
      END;
      /

      <<myBlockLabel1>>
      BEGIN
      NULL;
      END;
      /

      BEGIN
      NULL;
      END myBlockLabel2;
      /

      <<myBlockLabel3>>
      BEGIN
      NULL;
      END myBlockLabel3;
      /

      <<myBlockLabel6>>
      BEGIN
      NULL;
      END myBlockLabel6;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Individual WHERE clause conditions should not be unconditionally true or false">
    <div class="paragraph">
      <p><code>WHERE clause conditions that reinforce or contradict the definitions of their columns are useless; they are always either unconditionally true or unconditionally false. For instance, there’s no point in including AND column IS NOT NULL</code> if the column is defined as non-null.</p>
    </div>

    <div class="paragraph">
      <p><strong>Noteworthy</strong></p>
    </div>

    <div class="paragraph">
      <p>This rule raises issues only when a <strong>Data Dictionary</strong> is provided during the analysis. See <a href="https://docs.sonarqube.org/latest/analysis/languages/plsql/" class="bare">[https://docs.sonarqube.org/latest/analysis/languages/plsql/](https://docs.sonarqube.org/latest/analysis/languages/plsql/)</a></p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TABLE product
      (id INT,
      name VARCHAR(6) NOT NULL,
      mfg_name VARCHAR(6),
      mfg_id INT
      ...

      SELECT name, price
      FROM product
      WHERE name is not null -- Noncompliant; always true. This column is NOT NULL
      AND mfg_name = 'Too long name' -- Noncompliant; always false. This column can contain only 6 characters
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="NATURAL JOIN queries should not be used">
    <div class="paragraph">
      <p><code>NATURAL JOIN</code> is a type of equi-join which implicitly compares all identically-named columns of the two tables. While this a feature which may seem convenient at first, it becomes hard to maintain over time.</p>
    </div>

    <div class="paragraph">
      <p>Consider an EMPLOYEE table with the columns FULL\_NAME, and DEPT\_ID, and a DEPARTMENT table with the columns DEPT\_ID, and NAME. A natural join between those tables will join on the DEPT\_ID column, which is the only identically-named column.</p>
    </div>

    <div class="paragraph">
      <p>But, if a new NAME column is later added to the EMPLOYEE table, then the join will be done on both DEPT\_ID and NAME. Natural joins make simple changes such as adding a column complicated and are therefore better avoided.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT *
      INTO employeeArray
      FROM employee
      NATURAL JOIN departement; -- Non-Compliant, the join predicate is implicit
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      SELECT *
      INTO employeeArray
      FROM employee
      JOIN departement
      USING (dept_id);  -- Compliant, explicit join predicate
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Object attributes should comply with a naming convention">
    <div class="paragraph">
      <p>Shared coding conventions allow teams to collaborate efficiently. This rule checks that object attribute names match the provided regular expression.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TYPE my_type AS OBJECT(
      foo__bar INTEGER             -- Non-Compliant
      );
      /

      DROP TYPE my_type;
      ```

      ```plsql Fix theme={null}
      CREATE TYPE my_type AS OBJECT(
      foo_bar INTEGER              -- Compliant
      );
      /

      DROP TYPE my_type;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Procedures should not contain RETURN statements">
    <div class="paragraph">
      <p>Procedures, unlike functions, do not return values. The <code>RETURN statement therefore, when used within a procedure, is used to prematurely end the procedure. However, having multiple exit points (i.e. more than the END</code> of the procedure itself), increases the complexity of the procedure and makes it harder to understand and debug.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE prcoedureWithReturn AS
      BEGIN
      RETURN; -- Noncompliant

      DBMS_OUTPUT.PUT_LINE('prcoedureWithReturn called'); -- This is actually unreachable
      END;
      BEGIN
      prcoedureWithReturn;
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="CASE should be used for sequences of simple tests">
    <div class="paragraph">
      <p>When a single primitive is tested against three or more values in an <code>IF, ELSIF chain, it should be converted to a CASE</code> instead for greater readability.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      x PLS_INTEGER := 0;
      BEGIN
      IF x = 0 THEN                     -- Noncompliant
      DBMS_OUTPUT.PUT_LINE('x = 0');
      ELSIF x = 1 THEN
      DBMS_OUTPUT.PUT_LINE('x = 1');
      ELSE
      DBMS_OUTPUT.PUT_LINE('x > 1');
      END IF;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      x PLS_INTEGER := 0;
      BEGIN
      CASE x
      WHEN 0 THEN
        DBMS_OUTPUT.PUT_LINE('x = 0');
      WHEN 1 THEN
        DBMS_OUTPUT.PUT_LINE('x = 1');
      ELSE
        DBMS_OUTPUT.PUT_LINE('x > 1');
      END CASE;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="TO_NUMBER should be used with a format model">
    <div class="paragraph">
      <p>The \`TO\_NUMBER function is converting the value of BINARY\_FLOAT, BINARY\_DOUBLE, CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of NUMBER datatype.</p>
    </div>

    <div class="paragraph">
      <p>Without providing the format of the input, TO\_NUMBER will consider the provided value is compliant with the default format. Relying on a default configuration is a source of error because it creates a dependency between the code and the configuration of the ORACLE server where this code is deployed.</p>
    </div>

    <div class="paragraph">
      <p>The behaviour of the TO\_NUMBER\` function will certainly not be the expected one if the configuration of the ORACLE server is changing.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      IF ( TO_NUMBER(p_string) >= 0 and TO_NUMBER(p_string) <= TO_NUMBER('50.00') ) THEN
      RETURN 1;
      ELSE
      RETURN 0;
      END IF;
      ```

      ```plsql Fix theme={null}
      IF ( TO_NUMBER(p_string, '99.99') >= 0 and TO_NUMBER(p_string, '99.99') <= TO_NUMBER('50.00','99.99') ) THEN
      RETURN 1;
      ELSE
      RETURN 0;
      END IF;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Types should follow a naming convention">
    <div class="paragraph">
      <p>Shared coding conventions allow teams to collaborate efficiently. This rule checks that type names match the provided regular expression.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      TYPE Collection_type_ IS VARRAY(42) OF PLS_INTEGER; -- Noncompliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      TYPE collectionType IS VARRAY(42) OF PLS_INTEGER;
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="The RELIES_ON clause should not be used">
    <div class="paragraph">
      <p>Since Oracle 11.2, <code>RELIES\_ON</code> has been deprecated because the dependencies of result cache-enabled functions are automatically computed.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE FUNCTION foo RETURN PLS_INTEGER RESULT_CACHE RELIES_ON(DUAL) AS -- Noncompliant
      BEGIN
      RETURN 0;
      END;
      /

      DROP FUNCTION foo;
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE FUNCTION foo RETURN PLS_INTEGER RESULT_CACHE AS
      BEGIN
      RETURN 0;
      END;
      /

      DROP FUNCTION foo;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="COALESCE should be preferred to NVL">
    <div class="paragraph">
      <p><code>NVL was introduced by Oracle Database during the 80’s. COALESCE is a more modern function part of ANSI-92 standard than can replace NVL. Use of COALESCE should be preferred to NVL</code> for performance reason if the two parameters provided have the same type. COALESCE is running faster than NVL thanks to its short-circuit evaluation of the parameters.</p>
    </div>

    <div class="paragraph">
      <p>In order to avoid "ORA-00932: inconsistent datatypes" error, double-check the two arguments of the NVL function have the same type before switching to COALESCE.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SELECT employee_uuid, NVL(TO_CHAR(bonus_pct), 'not this year') "Bonus" FROM employees
      ```

      ```plsql Fix theme={null}
      SELECT employee_uuid, COALESCE(TO_CHAR(bonus_pct), 'not this year') "Bonus" FROM employees
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="EXECUTE IMMEDIATE should be used instead of DBMS_SQL procedure calls">
    <div class="paragraph">
      <p><code>EXECUTE IMMEDIATE</code> is easier to use and understand than the DBMS\_SQL package’s procedures. It should therefore be preferred, when possible.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE myTable(
      foo VARCHAR2(42)
      );

      CREATE PROCEDURE drop_table(tableName VARCHAR2) AS
      cursorIdentifier INTEGER;
      BEGIN
      cursorIdentifier := DBMS_SQL.OPEN_CURSOR; -- Compliant; this is not a procedure call
      DBMS_SQL.PARSE(cursorIdentifier, 'DROP TABLE ' || tableName, DBMS_SQL.NATIVE); -- Noncompliant
      DBMS_SQL.CLOSE_CURSOR(cursorIdentifier); -- Noncompliant

      DBMS_OUTPUT.PUT_LINE('Table ' || tableName || ' dropped.');
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(cursorIdentifier); -- Noncompliant
      END;
      /

      BEGIN
      drop_table('myTable');
      END;
      /

      DROP PROCEDURE drop_table;
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE myTable(
      foo VARCHAR2(42)
      );

      CREATE PROCEDURE drop_table(tableName VARCHAR2) AS
      cursorIdentifier INTEGER;
      BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ' || tableName;
      DBMS_OUTPUT.PUT_LINE('Table ' || tableName || ' dropped.');
      END;
      /

      BEGIN
      drop_table('myTable');
      END;
      /

      DROP PROCEDURE drop_table;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="DML events clauses should not include multiple OF clauses">
    <div class="paragraph">
      <p>The DML events clause of a trigger is not meant to be used with multiple \`OF conditions. When it is, only the last one will actually be taken into account, without any error message being produced. This can lead to counter-intuitive code.</p>
    </div>

    <div class="paragraph">
      <p>Only the UPDATE event should have an OF\` condition, and there should be at most one occurence of it.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE TRIGGER myTrigger
      BEFORE UPDATE OF firstName OR UPDATE OF lastName -- Noncompliant - will *only* be triggered on updates of lastName!
      ON myTable
      FOR EACH ROW
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE TRIGGER myTrigger
      BEFORE UPDATE OF firstName, lastName             -- Compliant - triggered on updates of firstName or/and lastName
      ON myTable
      FOR EACH ROW
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="EXIT should not be used in loops">
    <div class="paragraph">
      <p>FOR and WHILE loops are structured control flow statements.</p>
    </div>

    <div class="paragraph">
      <p>A FOR loop will iterate once for each element in the range, and the WHILE iterates for as long as a condition holds.</p>
    </div>

    <div class="paragraph">
      <p>However, inserting an <code>EXIT</code> statement within the loop breaks this structure, reducing the code’s readability and making it harder to debug.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      TYPE myCollectionType IS VARRAY(10) OF VARCHAR2(42);
      myCollection myCollectionType := myCollectionType('Foo', 'Bar', NULL, 'Baz', 'Qux');

      i PLS_INTEGER;
      BEGIN
      i := 1;
      WHILE i <= myCollection.LAST LOOP
      EXIT WHEN myCollection(i) IS NULL; -- Noncompliant, breaks the structure of the WHILE

      DBMS_OUTPUT.PUT_LINE('Got: ' || myCollection(i));
      i := i + 1;
      END LOOP;
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      TYPE myCollectionType IS VARRAY(10) OF VARCHAR2(42);
      myCollection myCollectionType := myCollectionType('Foo', 'Bar', NULL, 'Baz', 'Qux');

      i PLS_INTEGER;
      BEGIN
      i := 1;
      WHILE i <= myCollection.LAST AND myCollection(i) IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE('Got: ' || myCollection(i));
      i := i + 1;
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="%TYPE and %ROWTYPE should be used">
    <div class="paragraph">
      <p>The use of <code>%TYPE and %ROWTYPE</code> is an easy way to make sure that a variable’s type always matches the type of the relevant column in an existing table. If the column type changes, then the variable changes with it.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE FUNCTION func1(acc_no IN NUMBER) 
      RETURN NUMBER 
      IS total NUMBER(11,2); -- Noncompliant
      BEGIN 
        SELECT order_total 
        INTO total -- total is assigned here: it could be defined as orders.order_total%TYPE
        FROM orders 
        WHERE customer_id = acc_no; 
        RETURN(total); 
      END;
      ```

      ```plsql Fix theme={null}
      CREATE FUNCTION func1(acc_no IN NUMBER) 
      RETURN NUMBER 
      IS total orders.order_total%TYPE;
      BEGIN 
        SELECT order_total 
        INTO total
        FROM orders 
        WHERE customer_id = acc_no; 
        RETURN(total); 
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Loops with at most one iteration should be refactored">
    <div class="paragraph">
      <p>A loop with at most one iteration is equivalent to the use of an \`IF statement to conditionally execute one piece of code. No developer expects to find such usage of a loop statement. If the initial intention of the author was really to conditionally execute one piece of code, an IF statement should be used in place.</p>
    </div>

    <div class="paragraph">
      <p>At worst that was not the initial intention of the author and so the body of the loop should be fixed to use the nested RETURN, EXIT, RAISE or GOTO\` statements in a more appropriate way.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      LOOP
      counter := counter + 1;
      dbms_output.put_line(counter);
      EXIT;   -- Noncompliant
      END LOOP;
      ```

      ```plsql Fix theme={null}
      LOOP
      counter := counter + 1;
      IF counter > 10 THEN
      EXIT;
      ELSE 
      dbms_output.put_line(counter);
      END IF;
      END LOOP;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="TO_DATE and TO_TIMESTAMP should be used with a datetime format model">
    <div class="paragraph">
      <p>The \`TO\_DATE and TO\_TIMESTAMP functions are converting char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to respectively a value of DATE or TIMESTAMP datatype.</p>
    </div>

    <div class="paragraph">
      <p>Without providing the format of the input char, TO\_DATE will consider the provided char is compliant with the default date format. Relying on a default configuration is a source of error because it creates a dependency between the code and the configuration of the ORACLE server where this code is deployed.</p>
    </div>

    <div class="paragraph">
      <p>According to the Oracle’s documentation "the default date format is determined implicitly by the NLS\_TERRITORY initialization parameter or can be set explicitly by the NLS\_DATE\_FORMAT parameter.". It means the behaviour of the TO\_DATE\` function will certainly not be the expected one if the configuration of the ORACLE server is changing.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SELECT TO_DATE( 'January 15, 2018, 11:00 A.M.')
      FROM DUAL;
      ```

      ```plsql Fix theme={null}
      SELECT TO_DATE( 'January 15, 2018, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.')
      FROM DUAL;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Queries should not SELECT too many columns">
    <div class="paragraph">
      <p>\`SELECT queries that return too many columns may be complex or difficult to maintain.</p>
    </div>

    <div class="paragraph">
      <p>This rule identifies queries that SELECT\` more than the specified number of columns.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT id, name, firstname, gender, height, weight, age -- Noncompliant
      INTO peopleArray
      FROM people
      WHERE age > 60;
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="DBMS_OUTPUT.PUT_LINE should not be used">
    <div class="paragraph">
      <p>The output of <code>DBMS\_OUTPUT.PUT\_LINE is not always visible, for example when SERVEROUTPUT is set to OFF</code>. Moreover, there is no standardized way to specify the importance of the message. It is better to use a logging mechanism instead.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      BEGIN
      DBMS_OUTPUT.PUT_LINE('An error occured'); -- Noncompliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Constant declarations should contain initialization assignments">
    <div class="paragraph">
      <p>Constants must be immediately initialized at declaration. They cannot be reassigned any value after the declaration, as they are constant. This rule prevents PLS-00322 exceptions from being raised at runtime.</p>
    </div>

    <div class="paragraph">
      <p>The following code snippet illustrates this rule:</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo CONSTANT PLS_INTEGER NULL; -- Noncompliant PLS-00322
      bar CONSTANT PLS_INTEGER NOT NULL; -- Noncompliant PLS-00322
      aa CONSTANT PLS_INTEGER; -- Noncompliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo CONSTANT PLS_INTEGER NULL :=42;
      bar CONSTANT PLS_INTEGER NOT NULL := 42;
      aa CONSTANT PLS_INTEGER := 42; -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="cursor%NOTFOUND should be used instead of NOT cursor%FOUND">
    <div class="paragraph">
      <p><code>cursor%NOTFOUND is clearer and more readable than NOT cursor%FOUND</code>, and is preferred.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      CURSOR c IS SELECT DUMMY FROM DUAL;
      x VARCHAR2(1);
      BEGIN
      OPEN c;
      FETCH c INTO x;
      IF NOT c%FOUND THEN  -- Noncompliant
      DBMS_OUTPUT.PUT_LINE('uh?');
      ELSE
      DBMS_OUTPUT.PUT_LINE('all good: ' || x);
      END IF;
      CLOSE c;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      CURSOR c IS SELECT DUMMY FROM DUAL;
      x VARCHAR2(1);
      BEGIN
      OPEN c;
      FETCH c INTO x;
      IF c%NOTFOUND THEN 
      DBMS_OUTPUT.PUT_LINE('uh?');
      ELSE
      DBMS_OUTPUT.PUT_LINE('all good: ' || x);
      END IF;
      CLOSE c;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Constraints should not be applied to types that cannot be constrained">
    <div class="paragraph">
      <p>Some types cannot be constrained, and attempting to do so results in the exception <code>PLS-00566: type name "..." cannot be constrained</code> being raised.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo BLOB(42); -- Noncompliant - raises PLS-00566: type name "BLOB" cannot be constrained
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo BLOB;
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Procedures and functions should be documented">
    <div class="paragraph">
      <p>Each function and procedure should be documented with a comment either just before or right after the <code>IS or AS</code> keyword it to explain its goal and how it works.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE FUNCTION my_function RETURN PLS_INTEGER AS
      BEGIN
      RETURN 42;
      END;
      /

      CREATE PACKAGE my_package AS

      PROCEDURE my_procedure;

      FUNCTION my_function RETURN PLS_INTEGER;

      END my_package;
      /
      ```

      ```plsql Fix theme={null}
      CREATE FUNCTION my_function RETURN PLS_INTEGER AS
      -- Computes the meaning of life
      BEGIN
      RETURN 42;
      END;
      /

      CREATE PACKAGE my_package AS

      -- This is documentation
      PROCEDURE my_procedure;

      /*
      This is documentation
      */
      FUNCTION my_function RETURN PLS_INTEGER;

      END my_package;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Size should be specified for string variables">
    <div class="paragraph">
      <p>String data types, such as <code>VARCHAR2 or NVARCHAR2 require a size constraint. Omitting the size results in the exception PLS-00215: String length constraints must be in range (1 .. 32767)</code> being raised.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo VARCHAR2; -- Noncompliant - raises PLS-00215
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo VARCHAR2(42); -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Complex IF statements should be replaced by CASE statements ">
    <div class="paragraph">
      <p>Complex chains of IF, ELSIF and ELSE statements should be replaced by the more readable CASE one. A complex IF statement has either several ELSIF clauses, or both an ELSIF and an ELSE clause.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      x PLS_INTEGER := 0;
      BEGIN
      IF x = 0 THEN                     -- Noncompliant
      DBMS_OUTPUT.PUT_LINE('x = 0');
      ELSIF x = 1 THEN
      DBMS_OUTPUT.PUT_LINE('x = 1');
      ELSIF x = 2 THEN
      DBMS_OUTPUT.PUT_LINE('x = 2');
      END IF;
      END;
      /

      DECLARE
      x PLS_INTEGER := 0;
      y PLS_INTEGER := 0;
      BEGIN
      IF x = 0 THEN                     -- Noncompliant
      DBMS_OUTPUT.PUT_LINE('x = 0, y = ?');
      ELSIF y = 1 THEN
      DBMS_OUTPUT.PUT_LINE('x != 0, y = 1');
      ELSE
      DBMS_OUTPUT.PUT_LINE('x != 0, y != 1');
      END IF;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      x PLS_INTEGER := 0;
      BEGIN
      CASE x
      WHEN 0 THEN
        DBMS_OUTPUT.PUT_LINE('x = 0');
      WHEN 1 THEN
        DBMS_OUTPUT.PUT_LINE('x = 1');
      WHEN 2 THEN
        DBMS_OUTPUT.PUT_LINE('x = 2');
      ELSE
        -- Do not forget the ELSE to prevent ORA-06592
        NULL;
      END CASE;
      END;
      /

      DECLARE
      x PLS_INTEGER := 0;
      y PLS_INTEGER := 0;
      BEGIN
      CASE                              -- Compliant
      WHEN x = 0 THEN
        DBMS_OUTPUT.PUT_LINE('x = 0, y = ?');
      WHEN y = 1 THEN
        DBMS_OUTPUT.PUT_LINE('x != 0, y = 1');
      ELSE
        DBMS_OUTPUT.PUT_LINE('x != 0, y != 1');
      END CASE;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Record fields should comply with a naming convention">
    <div class="paragraph">
      <p>Shared coding conventions allow teams to collaborate efficiently. This rule checks that all record field names match the provided regular rexpression.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      TYPE my_type IS RECORD(
      foo__bar PLS_INTEGER   -- Non-Compliant
      );
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      TYPE my_type IS RECORD(
      foo_bar PLS_INTEGER    -- Compliant
      );
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Oracles join operator (+) should not be used">
    <div class="paragraph">
      <p>Developers should use the <code>FROM ... OUTER JOIN syntax rather than the Oracle join operator (). The reason is that outer join queries that use  are subject to several restrictions which do not apply to the FROM ... OUTER JOIN syntax. For instance, a WHERE condition containing the + operator cannot be combined with another condition using the OR</code> logical operator.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      -- Noncompliant
      SELECT *
      INTO employeesArray
      FROM employee, department
      WHERE employee.DepartmentID = department.ID(+);
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      -- Compliant
      SELECT *
      INTO employeesArray
      FROM employee LEFT OUTER JOIN department
      ON employee.DepartmentID = department.ID;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="WHEN OTHERS should not be the only exception handler">
    <div class="paragraph">
      <p>Before trapping all possible exceptions, it is best to try to trap the specific ones and try to recover from those.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE hitCounter
      (
      page VARCHAR2(42),
      hits NUMBER,
      CONSTRAINT pk PRIMARY KEY (page)
      );

      CREATE PROCEDURE hitPage(pageIn VARCHAR2) AS
      BEGIN
      INSERT INTO hitCounter VALUES (pageIn, 1);
      EXCEPTION -- Noncompliant, the only exception handler is WHEN OTHERS
      WHEN OTHERS THEN
      IF SQLCODE = -1 THEN
        UPDATE hitCounter SET hits = hits + 1 WHERE page = pageIn;
      ELSE
        DBMS_OUTPUT.PUT_LINE('An unknown error occured!');
      END IF;
      END;
      /

      BEGIN
      hitPage('index.html');
      hitPage('index.html');
      END;
      /

      SELECT * FROM hitCounter;

      DROP PROCEDURE hitPage;
      DROP TABLE hitCounter;
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE hitCounter
      (
      page VARCHAR2(42),
      hits NUMBER,
      CONSTRAINT pk PRIMARY KEY (page)
      );

      CREATE PROCEDURE hitPage(pageIn VARCHAR2) AS
      BEGIN
      INSERT INTO hitCounter VALUES (pageIn, 1);
      EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
      UPDATE hitCounter SET hits = hits + 1 WHERE page = pageIn;
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unknown error occured!');
      END;
      /

      BEGIN
      hitPage('index.html');
      hitPage('index.html');
      END;
      /

      SELECT * FROM hitCounter;

      DROP PROCEDURE hitPage;
      DROP TABLE hitCounter;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FULL OUTER JOINS should be used with caution">
    <div class="paragraph">
      <p>Full outer joins aren’t in common use, and as a result many developers don’t really understand them. Therefore, each use of this language feature should be reviewed.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT *
      BULK COLLECT INTO result
      FROM DUAL d1
      FULL OUTER JOIN DUAL d2 ON d1.dummy != d2.dummy; -- Noncompliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="SYNCHRONIZE should not be used">
    <div class="paragraph">
      <p><code>SYNCHRONIZE was introduced by Oracle as a recovery mechanism in case there is a loss of synchronization between what is in memory and what is displayed. It works but it comes with a price; each call to SYNCHRONIZE</code> generates a network round-trip between the server and the Forms client. Most of the time it should be avoided or used with caution as explicitly stated in the Oracle Forms documentation.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SYNCHRONIZE;
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Procedures should have parameters">
    <div class="paragraph">
      <p>Procedures which don’t accept parameters are likely to either not be reused that often or to depend on global variables instead. Refactoring those procedures to take parameters will make them both more flexible and reusable.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      name VARCHAR2(42) := 'John';

      PROCEDURE print_name; -- Noncompliant

      PROCEDURE print_name AS -- Noncompliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Name: ' || name);
      END;

      BEGIN
      print_name;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE print_name(name VARCHAR2) AS -- Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Name: ' || name);
      END;
      BEGIN
      print_name('John');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="END LOOP should be followed by a semicolon">
    <div class="paragraph">
      <p>Labeled loops are useful, especially when the code is badly indented, to match the begin and end of each loop. However, those labels, if used, must appear on the same line as the "END" keyword in order to avoid any confusion. Indeed, the label might otherwise be seen as a procedure call.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE foo AS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('foo was called!');
      END;
      BEGIN
      LOOP
      EXIT;
      END LOOP -- The semicolon was forgotten

      foo; -- Noncompliant, This is interpreted as a label of the previous FOR loop, not as a procedure call to foo!

      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE foo AS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('foo was called!');
      END;
      BEGIN

      <<myLoopLabel>>
      LOOP
      EXIT;
      END LOOP myLoopLabel;

      foo; -- Correctly interpreted as a procedure call to foo
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Inserts should include values for non-null columns">
    <div class="paragraph">
      <p>Any insert which omits a value for a <code>NOT NULL</code> column in a database table will be automatically rejected by the database unless a default value has been specified for the column.</p>
    </div>

    <div class="paragraph">
      <p><strong>Noteworthy</strong></p>
    </div>

    <div class="paragraph">
      <p>This rule raises issues only when a <strong>Data Dictionary</strong> is provided during the analysis. See <a href="https://docs.sonarqube.org/latest/analysis/languages/plsql/" class="bare">[https://docs.sonarqube.org/latest/analysis/languages/plsql/](https://docs.sonarqube.org/latest/analysis/languages/plsql/)</a></p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      INSERT INTO MY_TABLE  -- Noncompliant; N2 value omitted
      (
      N1
      )
      VALUES
      (
      1
      )
      ```

      ```plsql Fix theme={null}
      INSERT INTO MY_TABLE  -- Compliant even though N3 value not supplied
      (
      N1,
      N2
      )
      VALUES
      (
      1,
      'Paul'
      )
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="WHEN OTHERS clauses should be used for exception handling">
    <div class="paragraph">
      <p>Ensure that every possible exception is caught by using a <code>WHEN OTHERS</code> clause.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      result PLS_INTEGER;
      custom_exception EXCEPTION;
      BEGIN
      result := 42 / 0;                            -- "Unexpected" division by 0

      RAISE custom_exception;
      EXCEPTION                                      -- Non-Compliant
      WHEN custom_exception THEN
      DBMS_OUTPUT.PUT_LINE ('custom_exception: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      result PLS_INTEGER;
      custom_exception EXCEPTION;
      BEGIN
      result := 42 / 0;                            -- "Unexpected" division by 0

      RAISE custom_exception;
      EXCEPTION                                      -- Compliant
      WHEN custom_exception THEN
      DBMS_OUTPUT.PUT_LINE ('custom_exception: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('other: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="UNION ALL should be preferred to UNION">
    <div class="paragraph">
      <p>\`UNION is significantly less performant compared to UNION ALL because it removes duplicated entries and run an internal DISTINCT to achieve this.</p>
    </div>

    <div class="paragraph">
      <p>UNION ALL is not removing duplicates and returns all the results from the queries. It performs faster in most of the cases compared to UNION. Nevertheless, the quantity of data returned by UNION ALL can be significantly more important than with UNION. On slow network, the performance gain to use UNION ALL instead of UNION can be challenged by the time lost to transfer more data than with UNION\`.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      -- if you care about not having duplicated entries, then UNION is the good choice
      SELECT EMAIL FROM EMPLOYEES UNION SELECT EMAIL FROM CUSTOMERS
      ```

      ```plsql Fix theme={null}
      -- if you don't care about duplicated entries in the results of this UNION, then UNION ALL should be preferred
      SELECT EMAIL FROM EMPLOYEES UNION ALL SELECT EMAIL FROM CUSTOMERS
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="END statements of labeled blocks should be labeled">
    <div class="paragraph">
      <p>Labeled blocks are useful, especially when the code is badly indented, to match the begin and end of each block. This check detects labeled blocks which are missing an ending label.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      <<myBlockLabel1>>
      BEGIN
      NULL;
      END; -- Noncompliant; this labeled loop has no ending label
      /

      BEGIN
      NULL; -- Compliant; not a labeled block
      END;
      /
      ```

      ```plsql Fix theme={null}
      <<myBlockLabel2>>
      BEGIN
      NULL;
      END myBlockLabel2;
      /

      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Parameter IN mode should be specified explicitly">
    <div class="paragraph">
      <p>By default, the parameter mode is <code>IN</code>. However, specifying it explicitly makes the code easier to read.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE printName(name VARCHAR2) AS -- Noncompliant; relies on default mode
      BEGIN
      DBMS_OUTPUT.PUT_LINE('name: ' || name);
      END;

      BEGIN
      printName('Foo');
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE printName(name IN VARCHAR2) AS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('name: ' || name);
      END;
      BEGIN
      printName('Foo');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="GOTO should not be used within loops">
    <div class="paragraph">
      <p>The use of <code>GOTO in general is arguable. However, when used within loops, GOTO</code> statements are even more evil, and they can often be replaced by other constructs.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      i PLS_INTEGER := 0;
      BEGIN
      LOOP
      IF i = 3 THEN
        GOTO loopEnd; -- Noncompliant
      END IF;

      DBMS_OUTPUT.PUT_LINE('i = ' || i);

      i := i + 1;
      END LOOP;

      <<loopEnd>>
      DBMS_OUTPUT.PUT_LINE('Loop end');
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      i PLS_INTEGER := 0;
      BEGIN
      LOOP
      EXIT WHEN i = 3; -- Compliant

      DBMS_OUTPUT.PUT_LINE('i = ' || i);

      i := i + 1;
      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Loop end');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Identical expressions should not be used on both sides of a binary operator">
    <div class="paragraph">
      <p>Using the same value on either side of a binary operator is almost always a mistake. In the case of logical operators, it is either a copy/paste error and therefore a bug, or it is simply wasted code, and should be simplified.</p>
    </div>

    <div class="paragraph">
      <p>This rule ignores operators <code>+, \* and ||, and expressions: 1=1, 1\<>1, 1!=1, 1\~=1 and 1^=1</code>.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SELECT code
      FROM Person
      WHERE first_name IS NULL OR first_name IS NULL; -- Noncompliant

      SELECT * FROM Users
      INNER JOIN Clients ON Clients.id = Clients.id; -- Noncompliant
      ```

      ```plsql Fix theme={null}
      SELECT code
      FROM Person
      WHERE first_name IS NULL OR last_name IS NULL;

      SELECT * FROM Users
      INNER JOIN Clients ON Clients.id = Users.id;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Comments should not be nested">
    <div class="paragraph">
      <p>PL/SQL does not support nested C-style (<code>/\* ... \*/</code>) comments.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      /*
      This is a comment block, for which the ending tag was omitted
      It may be difficult to figure out that the following line of code is actually commented


      variable = function_call();
      /* variable contains the result, this is not allowed, as it is an attempt to create an inner comment */
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Deprecated LONG and LONG RAW datatypes should no longer be used">
    <div class="paragraph">
      <p>The <code>LONG and LONG RAW datatypes are deprecated and Oracle recommends to migrate them to the LOB datatypes CLOB, NCLOB or BLOB</code>.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TABLE images(
      data LONG RAW
      );
      ```

      ```plsql Fix theme={null}
      CREATE TABLE images(
      data BLOB
      );
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Related IF/ELSIF statements and WHEN clauses in a CASE should not have the same condition">
    <div class="paragraph">
      <p>A <code>CASE and a chain of IF/ELSIF statements is evaluated from top to bottom. At most, only one branch will be executed: the first one with a condition that evaluates to true</code>.</p>
    </div>

    <div class="paragraph">
      <p>Therefore, duplicating a condition automatically leads to dead code. Usually, this is due to a copy/paste error. At best, it’s simply dead code and at worst, it’s a bug that is likely to induce further bugs as the code is maintained, and obviously it could lead to unexpected behavior.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      IF param == 1 THEN
      x := 'A';
      ELSIF param == 2 THEN
      x := 'B';
      ELSIF param == 1 THEN -- Noncompliant, for sure this is a bug
      x := 'C';
      END IF;

      result := CASE param
      WHEN 1 THEN 'A'
      WHEN 2 THEN 'B'
      WHEN 1 THEN 'C'  -- Noncompliant
      ELSE 'D'
      END;
      ```

      ```plsql Fix theme={null}
      IF param == 1 THEN
      result := 'A';
      ELSIF param == 2 THEN
      result := 'B';
      ELSIF param == 3 THEN
      result := 'C';
      END IF;

      result := CASE param
      WHEN 1 THEN 'A'
      WHEN 2 THEN 'B'
      WHEN 3 THEN 'C'
      ELSE 'D'
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="CREATE OR REPLACE should be used instead of CREATE">
    <div class="paragraph">
      <p>When creating a function, procedure, package, package body, type, type body, trigger or library, it is a good practice replace the existing one to avoid errors.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE FUNCTION my_function RETURN PLS_INTEGER AS -- Noncompliant
      BEGIN
      RETURN 42;
      END;
      /
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE FUNCTION my_function RETURN PLS_INTEGER AS -- Compliant, no error even if the function already exists
      BEGIN
      RETURN 42;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Sensitive SYS owned functions should not be used">
    <div class="paragraph">
      <p>Some Oracle packages contain powerful SYS-owned functions that can be used to perform malicious operations. For instance, <code>DBMS\_SYS\_SQL.PARSE\_AS\_USER</code> can be used to execute a statement as another user.</p>
    </div>

    <div class="paragraph">
      <p>Most programs do not need those functions and this rule helps identify them in order to prevent security risks.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      c INTEGER;
      sqltext VARCHAR2(100) := 'ALTER USER system IDENTIFIED BY hacker'; -- Might be injected by the user
      BEGIN
      c := SYS.DBMS_SYS_SQL.OPEN_CURSOR();                               -- Noncompliant

      -- Will change 'system' user's password to 'hacker'
      SYS.DBMS_SYS_SQL.PARSE_AS_USER(c, sqltext, DBMS_SQL.NATIVE, UID);  -- Non-Compliant

      SYS.DBMS_SYS_SQL.CLOSE_CURSOR(c);                                  -- Noncompliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Anchored types should not be constrained">
    <div class="paragraph">
      <p>Anchored types, i.e. those specified using either <code>%TYPE or %ROWTYPE, cannot be constrained. Trying to do so results in the exception PLS-00573: cannot constrain scale, precision, or range of an anchored type being raised.</code></p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo DUAL.DUMMY%TYPE(42); -- Non-Compliant - raises PLS-00573
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo DUAL.DUMMY%TYPE; -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Function and procedure parameters should comply with a naming convention">
    <div class="paragraph">
      <p>Each function and procedure parameter name must match a given regular expression.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      FUNCTION myfunction2(parameter_ PLS_INTEGER) RETURN PLS_INTEGER; -- Noncompliant

      PROCEDURE myprocedure2(parameter_ PLS_INTEGER); -- Noncompliant

      FUNCTION myfunction2(parameter_ PLS_INTEGER) RETURN PLS_INTEGER AS -- Noncompliant
      BEGIN
      RETURN 42;
      END;

      PROCEDURE myprocedure2(parameter_ PLS_INTEGER) AS -- Noncompliant
      BEGIN
      NULL;
      END;
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      FUNCTION myfunction1(parameter PLS_INTEGER) RETURN PLS_INTEGER; -- Compliant

      PROCEDURE myprocedure1(parameter PLS_INTEGER); -- Compliant

      FUNCTION myfunction1(parameter PLS_INTEGER) RETURN PLS_INTEGER AS -- Compliant
      BEGIN
      RETURN 42;
      END;

      PROCEDURE myprocedure1(parameter PLS_INTEGER) AS -- Compliant
      BEGIN
      NULL;
      END;

      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Cursor parameters should follow a naming convention">
    <div class="paragraph">
      <p>Shared coding conventions allow teams to collaborate efficiently. This rule checks that cursor parameters match the provided regular expression.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TABLE employee(
      name VARCHAR2(42)
      );

      DECLARE
      CURSOR mycursor2(Employee-name-parameter_ VARCHAR2) RETURN employee%ROWTYPE; -- Noncompliant

      CURSOR mycursor2(Employee-name-parameter_ VARCHAR2) RETURN employee%ROWTYPE IS SELECT * FROM employee WHERE name = Employee-name-parameter_; -- Noncompliant
      BEGIN
      NULL;
      END;
      /

      DROP TABLE employee;
      ```

      ```plsql Fix theme={null}
      CREATE TABLE employee(
      name VARCHAR2(42)
      );

      DECLARE
      CURSOR mycursor2(employeeNameParameter VARCHAR2) RETURN employee%ROWTYPE;

      CURSOR mycursor2(employeeNameParameter VARCHAR2) RETURN employee%ROWTYPE IS SELECT * FROM employee WHERE name = employeeNameParameter;
      BEGIN
      NULL;
      END;
      /

      DROP TABLE employee;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Pipelined functions should have at least one PIPE ROW statement and not return an expression (PLS-00633)">
    <div class="paragraph">
      <p>Pipelined functions offers the ability to create programmatically generated tables.</p>
    </div>

    <div class="paragraph">
      <p>One of the benefits of such functions is that they reduce memory consumption as results are not all kept in memory before being returned.</p>
    </div>

    <div class="paragraph">
      <p>Instead of relying on \`RETURN, PIPE ROW must be used to return the results, one row at a time.</p>
    </div>

    <div class="paragraph">
      <p>Trying to return an expression from a pipelined function raises PLS-00633: RETURN statement in a pipelined function cannot contain an expression\`</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE TYPE myScalarType AS OBJECT
      (
      dummy   VARCHAR2(42)
      )
      /
      CREATE OR REPLACE TYPE myTableType AS TABLE OF myScalarType;
      /

      CREATE OR REPLACE FUNCTION foo RETURN myTableType PIPELINED AS  -- Noncompliant, should contain at least one PIPE ROW
      result myTableType := myTableType();
      BEGIN
      FOR i IN 1 .. 3 LOOP
      result.EXTEND;
      result(i) := myScalarType('Dummy ' || i);
      END LOOP;

      RETURN result;  -- Noncompliant, will raise PLS-00633
      END;
      /

      SELECT * FROM TABLE(foo());

      DROP FUNCTION foo;
      DROP TYPE myTableType;
      DROP TYPE myScalarType;
      ```

      ```plsql Fix theme={null}
      -- ...

      CREATE OR REPLACE FUNCTION foo RETURN myTableType PIPELINED AS
      BEGIN
      FOR i IN 1 .. 3 LOOP
      PIPE ROW(myScalarType('Dummy ' || i));
      END LOOP;

      RETURN;
      END;
      /

      -- ...
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Whitespace and control characters in string literals should be explicit">
    <div class="paragraph">
      <p>New lines and control characters can be injected in the source code by bad manipulations. Control characters aren’t visible to maintainers, so whether or not they are actually wanted should be double-checked. Note that this rule can optionally also report violations on literals containing the tabulation character.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      BEGIN
      /* Non-Compliant */ DBMS_OUTPUT.PUT_LINE('Hello
      world!');

      DBMS_OUTPUT.PUT_LINE('Hello'); -- Compliant, this is preferred
      DBMS_OUTPUT.PUT_LINE('world!');
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="All code should be reachable">
    <div class="paragraph">
      <p>Jump statements (\`EXIT, CONTINUE, RETURN, RAISE, and RAISE\_APPLICATION\_ERROR), move control flow out of the current code block. So any statements that come after a jump are dead code.</p>
    </div>

    <div class="paragraph">
      <p>This rule detects for statements that follow:</p>
    </div>

    <div class="ulist">
      <ul>
        <li>
          <p>EXIT without a WHEN</p>
        </li>

        <li>
          <p>CONTINUE without a WHEN</p>
        </li>

        <li>
          <p>RETURN</p>
        </li>

        <li>
          <p>RAISE</p>
        </li>

        <li>
          <p>RAISE\_APPLICATION\_ERROR\`</p>
        </li>
      </ul>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      BEGIN
      LOOP
      DBMS_OUTPUT.PUT_LINE('This will be printed out');
      EXIT;

      DBMS_OUTPUT.PUT_LINE('This will NEVER be printed out'); -- Non-Compliant
      END LOOP;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      BEGIN
      LOOP
      DBMS_OUTPUT.PUT_LINE('This will be printed out');
      EXIT;
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="In labeled loops EXIT should exit the label">
    <div class="paragraph">
      <p>Labeled loops are useful, especially when the code is badly indented, to match the begin and end of each loop. Within a labeled loop, the code’s maintainability is increased by explicitly providing the loop’s label in every <code>EXIT</code> statement. Indeed, if a nested loop is added afterwards, it is clear which loop has to be exited.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      <<myLoopLabel1>>
      LOOP
      EXIT; -- Noncompliant, the loop label is missing
      END LOOP myLoopLabel1;

      LOOP
      EXIT; -- Compliant, this EXIT is not in a labeled loop
      END LOOP;
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      <<myLoopLabel1>>
      LOOP
      EXIT myLoopLabel1;
      END LOOP myLoopLabel1;

      LOOP
      EXIT;
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="An ORDER BY direction should be specified explicitly">
    <div class="paragraph">
      <p><code>ASC or DESC should be specified for every column of an ORDER BY</code> clause to improve readability.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT col1, col2, col3
      BULK COLLECT INTO result
      FROM my_table
      ORDER BY
      col1 ASC,
      col2,            -- Noncompliant - ASC or DESC should be specified
      col3 DESC;
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      SELECT col1, col2, col3
      BULK COLLECT INTO result
      FROM my_table
      ORDER BY
      col1 ASC,
      col2 ASC,
      col3 DESC;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="CREATE_TIMER should not be used">
    <div class="paragraph">
      <p><code>CREATE\_TIMER</code> is generating network traffic each time the timer is fired. It’s probably totally fine for a timer being executed every hour but generally, this is used to provide clocks components that are going to generate network traffic every second or more.</p>
    </div>

    <div class="paragraph">
      <p>It is recommended by Oracle to examine timers and replace them with JavaBeans.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      timer := CREATE_TIMER('foo', 1000, REPEAR)
      ENDl
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="At least one exception should be handled in an exception block">
    <div class="paragraph">
      <p>Shadowing all exceptions with NULL statements indicates that no error handling has been done for a given block of code. This is a common bad-practice and only the non-relevant exceptions should be ignored (and a comment is welcome in such cases).</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN 
      SELECT value 
      INTO :hits 
      FROM hitCounter 
      WHERE pageIn = 'Sample'; 
      EXCEPTION                    -- Noncompliant
      WHEN OTHERS THEN 
      NULL; 
      END; 

      BEGIN 
      SELECT value 
      INTO :hits 
      FROM hitCounter 
      WHERE pageIn = 'Sample'; 
      EXCEPTION                    -- Noncompliant
      WHEN TOO_MANY_ROWS THEN
      NULL;
      WHEN OTHERS THEN 
      NULL; 
      END;
      ```

      ```plsql Fix theme={null}
      BEGIN 
      SELECT value 
      INTO :hits 
      FROM hitCounter 
      WHERE pageIn = 'Sample'; 
      EXCEPTION                    -- Compliant
      WHEN NO_DATA_FOUND THEN
      hits := 0;
      WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('Error: too many entries for Sample');
      WHEN OTHERS THEN 
      -- Cannot do more in this case
      NULL; 
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="NUMBER variables should be declared with precision">
    <div class="paragraph">
      <p>Declaring a \`NUMBER variable without any precision wastes memory because Oracle supports up to 38 decimal digits by default (or the maximum supported by your system, whichever is less). If you don’t need that large a value, you should specify whatever matches your needs. This will save memory and provide extra integrity checking on input.</p>
    </div>

    <div class="paragraph">
      <p>This rule also applies to some NUMBER subtypes as well: NUMERIC, DEC, and DECIMAL\`.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      var1 NUMBER; -- Noncompliant
      var2 NUMERIC; -- Noncompliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      var1 NUMBER(9,2);
      var2 NUMERIC(4,0);
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Loop start and end labels should match">
    <div class="paragraph">
      <p>Labeled loops are useful, especially when the code is badly indented, to match the begin and end of each loop. This rule verifies that loop start and end labels match, when both are specified.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      LOOP
      EXIT;
      END LOOP; -- Compliant, this loop has no label at all

      <<myLoopLabel1>>
      LOOP
      EXIT;
      END LOOP; -- Compliant, this loop only has a start label

      LOOP
      EXIT;
      END LOOP myLoopLabel2; -- Compliant, this loop only has an end label

      <<myLoopLabel4>>
      LOOP
      EXIT;
      END LOOP myLoopLabel5; -- Noncompliant, label mismatch

      <<myLoopLabel6>>
      <<myLoopLabel7>>
      LOOP
      EXIT;
      END LOOP myLoopLabel7; -- Noncompliant, several start labels mismatch
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      LOOP
      EXIT;
      END LOOP;

      <<myLoopLabel1>>
      LOOP
      EXIT;
      END LOOP;

      LOOP
      EXIT;
      END LOOP myLoopLabel2;

      <<myLoopLabel4>>
      LOOP
      EXIT;
      END LOOP myLoopLabel4;

      <<myLoopLabel7>>
      LOOP
      EXIT;
      END LOOP myLoopLabel7;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="EXIT WHEN should be used rather than IF ... THEN EXIT; END IF;">
    <div class="paragraph">
      <p>The <code>EXIT WHEN syntax can exit a loop depending on a condition. It should be preferred to the more verbose and error-prone IF ... THEN EXIT; END IF;</code> syntax.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      i PLS_INTEGER;
      BEGIN

      i := 0;
      LOOP
      IF i > 10 THEN -- Noncompliant
         EXIT;
      END IF;

      DBMS_OUTPUT.PUT_LINE('i = ' || i);
      i := i + 1;
      END LOOP;

      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      i PLS_INTEGER;
      BEGIN

      i := 0;
      LOOP
      EXIT WHEN i > 10;

      DBMS_OUTPUT.PUT_LINE('i = ' || i);
      i := i + 1;
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="IF ... ELSEIF constructs should end with ELSE clauses">
    <div class="paragraph">
      <p>This rule applies whenever an \`IF statement is followed by one or</p>
    </div>

    <div class="paragraph">
      <p>more ELSEIF statements; the final ELSEIF should be followed by an ELSE statement.</p>
    </div>

    <div class="paragraph">
      <p>The requirement for a final ELSE statement is defensive programming.</p>
    </div>

    <div class="paragraph">
      <p>The ELSE statement should either take appropriate action or contain</p>
    </div>

    <div class="paragraph">
      <p>a suitable comment as to why no action is taken. This is consistent with the</p>
    </div>

    <div class="paragraph">
      <p>requirement to have a final ELSE clause in a CASE\`</p>
    </div>

    <div class="paragraph">
      <p>statement.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      IF my_variable = 0 THEN
      do_something;
      ELSIF my_variable = 1 THEN
      do_something_else;
      END IF;
      ```

      ```plsql Fix theme={null}
      IF my_variable = 0 THEN
      do_something;
      ELSIF my_variable = 1 THEN
      do_something_else;
      ELSE
      -- Nothing has to be done.
      NULL;
      END IF;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Improper constraint forms should not be used">
    <div class="paragraph">
      <p>Not every data type supports the <code>RANGE or scale constraints. Using these constraints on incompatible types results in an PLS-00572: improper constraint form used</code> exception being raised.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo INTEGER RANGE 0 .. 42; -- Non-Compliant - raises PLS-00572 as NUMBER does not support the RANGE constraint
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo INTEGER; -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Assignments of default values for variables should be located in the initialization section">
    <div class="paragraph">
      <p>Assigning default values to variables in an initialization section is a good practice as it increases the readability of the code: when starting reading a block, one will know exactly which variables have default values.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      var1 integer;
      var2 integer;
      var3 integer;
      BEGIN
      var1 := 1; -- Noncompliant
      var2 := 2; -- Noncompliant
      var3 := var1 + var2;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      var1 integer := 1;
      var2 integer := 2;
      var3 integer;
      BEGIN
      var3 := var1 + var2;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Tables should be aliased">
    <div class="paragraph">
      <p>When multiple tables are involved in a query, using table aliases helps to make it more understandable and keeps it short.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT
      name,
      firstname,
      location
      INTO employeesArray
      FROM employee -- Noncompliant - should be aliased
      INNER JOIN department -- Noncompliant - should be aliased
      ON employee.DepartmentID = department.ID;
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      SELECT
      empl.name,
      empl.firstname,
      dpt.location
      INTO employeesArray
      FROM employee empl
      INNER JOIN department dpt
      ON empl.DepartmentID = dpt.ID;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="SIMPLE_INTEGER should be used instead of PLS_INTEGER">
    <div class="paragraph">
      <p>ORACLE 11g introduced the \`SIMPLE\_INTEGER data type, which is a sub-type of PLS\_INTEGER, and covers the same range. There are three main differences between the two types:</p>
    </div>

    <div class="ulist">
      <ul>
        <li>
          <p>SIMPLE\_INTEGER is always NOT NULL. So when the value of the declared variable is never going to be null, you can declare it as SIMPLE\_INTEGER.</p>
        </li>

        <li>
          <p>You will never face a numeric overflow using SIMPLE\_INTEGER because this data type wraps around without giving any error.</p>
        </li>

        <li>
          <p>The SIMPLE\_INTEGER data type gives a major performance boost over PLS\_INTEGER when the code is compiled in "NATIVE" mode, because arithmetic operations on SIMPLE\_INTEGER\` type are performed directly at the hardware level.</p>
        </li>
      </ul>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      v1 PLS_INTEGER; -- Noncompliant
      v2 VARCHAR2(10);
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      v1 SIMPLE_INTEGER := 42;
      v2 VARCHAR2(10);
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Unary prefix operators should not be repeated">
    <div class="paragraph">
      <p>The repetition of a prefix operator (<code>+, -, or NOT</code>) is usually a typo. The second operator invalidates the first one:</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      IF NOT ( NOT foo = 5 ) THEN  -- Noncompliant: equivalent to "foo = 5"
      value := ++1;              -- Noncompliant: equivalent to "+1"
      END IF;
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="ROWNUM should not be used at the same query level as ORDER BY">
    <div class="paragraph">
      <p>Oracle’s <code>ROWNUM is a pseudo column that numbers the rows in a result set. Unfortunately, it numbers the rows in the set <em>before</em> ordering is applied. So combining the two in the same query won’t get you the results you expect. Instead, you should move your selection and ordering into a subquery, and use ROWNUM</code> only on the outer query.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SELECT fname, lname, deptId
      FROM employee
      WHERE rownum <= 10
      ORDER BY salary  -- Noncompliant
      ```

      ```plsql Fix theme={null}
      SELECT * 
      FROM ( SELECT fname, lname, deptId
      FROM employee
      ORDER BY salary
      )
      WHERE rownum <= 10
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="END statements of labeled loops should be labeled">
    <div class="paragraph">
      <p>Labeled loops are useful, especially when the code is badly indented, to match the begin and end of each loop. This rule raises an issue when the end of a labeled loop is unlabeled.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      <<myLoopLabel1>>
      LOOP
      EXIT;
      END LOOP; -- Noncompliant; this labeled loop has no ending label

      LOOP
      EXIT;
      END LOOP; -- Compliant; not a labeled loop
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      <<myLoopLabel1>>
      LOOP
      EXIT;
      END LOOP myLoopLabel1;

      LOOP
      EXIT;
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Names should not be reused in inner scopes">
    <div class="paragraph">
      <p>Using the same name for multiple purposes reduces the understandability of the code and might eventually lead to bugs.</p>
    </div>

    <div class="paragraph">
      <p>This rule verifies that no name is reused in an inner scope.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      <<foo>>                                    -- Compliant
      DECLARE
      a CONSTANT PLS_INTEGER := 0;
      BEGIN
      <<foo>>                                  -- Non-Compliant
      DECLARE
      b CONSTANT PLS_INTEGER := 42;
      BEGIN
      DBMS_OUTPUT.PUT_LINE('x = ' || foo.b); -- Confusing
      END;
      END;
      /
      ```

      ```plsql Fix theme={null}
      <<foo>>                                    -- Compliant
      DECLARE
      a CONSTANT PLS_INTEGER := 0;
      BEGIN
      <<bar>>                                  -- Compliant
      DECLARE
      b CONSTANT PLS_INTEGER := 42;
      BEGIN
      DBMS_OUTPUT.PUT_LINE('x = ' || bar.b); -- Clear
      END;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Global public variables should not be defined">
    <div class="paragraph">
      <p>When data structures (scalar variables, collections, cursors) are declared in the package specification (not within any specific program), they can be referenced directly by any program running in a session with <code>EXECUTE</code> rights to the package.</p>
    </div>

    <div class="paragraph">
      <p>Instead, declare all package-level data in the package body and provide getter and setter functions in the package specification. Developers can then access the data using these methods and will automatically follow all rules you set upon data modification.</p>
    </div>

    <div class="paragraph">
      <p>By doing so you can guarantee data integrity, change your data structure implementation, and also track access to those data structures.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      -- Package specification
      CREATE PACKAGE employee AS
      name VARCHAR2(42); -- Non-Compliant
      END employee;
      /

      DROP PACKAGE employee;
      ```

      ```plsql Fix theme={null}
      -- Package specification
      CREATE PACKAGE employee AS
      PROCEDURE setName (newName VARCHAR2);
      FUNCTION getName RETURN VARCHAR2;
      END employee;
      /

      -- Package body
      CREATE PACKAGE BODY employee AS
      name VARCHAR2(42);

      PROCEDURE setName (newName VARCHAR2) IS
      BEGIN
       name := newName;
      END;

      FUNCTION getName RETURN VARCHAR2 IS
      BEGIN
       RETURN name;
      END;
      END employee;
      /

      DROP PACKAGE BODY employee;

      DROP PACKAGE employee;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Native SQL joins should be used">
    <div class="paragraph">
      <p>SQL is an extremely powerful and hard to master language. It may be tempting to emulate SQL joins in PL/SQL using nested cursor loops, but those are not optimized by Oracle at all. In fact, they lead to numerous context switches between the SQL and PL/SQL engines, and those switches have a highly negative impact on performance. It is therefore much better to replace nested PL/SQL cursor loops with native SQL joins.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE countriesTable(
      countryName VARCHAR2(42)
      );

      CREATE TABLE citiesTable(
      cityName VARCHAR2(42)
      );

      INSERT INTO countriesTable VALUES('India');
      INSERT INTO countriesTable VALUES('Switzerland');
      INSERT INTO countriesTable VALUES('United States');

      INSERT INTO citiesTable VALUES('Berne');
      INSERT INTO citiesTable VALUES('Delhi');
      INSERT INTO citiesTable VALUES('Bangalore');
      INSERT INTO citiesTable VALUES('New York');

      BEGIN
      FOR countryRecord IN (SELECT countryName FROM countriesTable) LOOP
      FOR cityRecord IN (SELECT cityName FROM citiesTable) LOOP -- Non-Compliant
        DBMS_OUTPUT.PUT_LINE('Country: ' || countryRecord.countryName || ', City: ' || cityRecord.cityName);
      END LOOP;
      END LOOP;
      END;
      /

      DROP TABLE citiesTable;

      DROP TABLE countriesTable;
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE countriesTable(
      countryName VARCHAR2(42)
      );

      CREATE TABLE citiesTable(
      cityName VARCHAR2(42)
      );

      INSERT INTO countriesTable VALUES('India');
      INSERT INTO countriesTable VALUES('Switzerland');
      INSERT INTO countriesTable VALUES('United States');

      INSERT INTO citiesTable VALUES('Berne');
      INSERT INTO citiesTable VALUES('Delhi');
      INSERT INTO citiesTable VALUES('Bangalore');
      INSERT INTO citiesTable VALUES('New York');

      BEGIN
      FOR myRecord IN (SELECT * FROM countriesTable CROSS JOIN citiesTable) LOOP -- Compliant
      DBMS_OUTPUT.PUT_LINE('Country: ' || myRecord.countryName || ', City: ' || myRecord.cityName);
      END LOOP;
      END;
      /

      DROP TABLE citiesTable;

      DROP TABLE countriesTable;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="EXCEPTION WHEN ... THEN clauses should do more than RAISE">
    <div class="paragraph">
      <p>An <code>EXCEPTION WHEN ... THEN clause that only rethrows the caught exception has the same effect as omitting the EXCEPTION</code> clause altogether and letting it bubble up automatically.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT 1/0;
      EXCEPTION
      WHEN ZERO_DIVIDE THEN
      RAISE; -- Noncompliant
      WHEN OTHERS THEN
      RAISE; -- Noncompliant
      END;
      ```

      ```plsql Fix theme={null}
      BEGIN
      SELECT 1/0;
      EXCEPTION
      WHEN ZERO_DIVIDE THEN
      -- do something to manage the division by zero
      COMMIT;

      WHEN OTHERS THEN
      ROLLBACK;
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Columns should be aliased">
    <div class="paragraph">
      <p>Consistently using aliases for column names is useful for several reasons. The main one is that the code is independant from potential database modifications - when a column has been renamed to comply with standards for instance. Another reason is to remove ambiguity when querying several tables that may have equivalent column names.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT
      emp.name, -- Noncompliant - should be aliased
      dpt.name -- Noncompliant - should be aliased
      INTO employeesArray
      FROM employee emp INNER JOIN department dpt
      ON emp.DepartmentID = dpt.ID;
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      SELECT
      emp.name employee_name, -- Compliant
      dpt.name departement_name -- Compliant
      INTO employeesArray
      FROM employee emp INNER JOIN department dpt
      ON emp.DepartmentID = dpt.ID;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Positional and named arguments should not be mixed in invocations">
    <div class="paragraph">
      <p>For better readability, and to prevent the <code>PLS-00312: a positional parameter association may not follow a named association</code> exception from being raised, do not mix named and positional argument invocations.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE sub(op1 PLS_INTEGER, op2 PLS_INTEGER) AS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Sub = ' || (op1 - op2));
      END;
      BEGIN

      sub(10, op2 => 2); -- Noncompliant
      sub(op1 => 10, 2); -- Noncompliant - raises PLS-00312: a positional parameter association may not follow a named association

      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE sub(op1 PLS_INTEGER, op2 PLS_INTEGER) AS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Sub = ' || (op1 - op2));
      END;
      BEGIN

      sub(10, 2); -- Compliant
      sub(op1 => 10, op2 => 2); -- Compliant

      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables should be declared only once in a scope">
    <div class="paragraph">
      <p>At most one declaration of a variable in a given scope is allowed in PL/SQL. The <code>PLS-00371</code> error will be raised at runtime when attempting to reference a variable declared more than once.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo VARCHAR2(42) := 'foo';
      foo VARCHAR2(42) := 'bar'; -- Non-Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE(foo); -- Raises PLS-00371: at most one declaration for 'FOO' is permitted
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo VARCHAR2(42) := 'foo';
      bar VARCHAR2(42) := 'bar'; -- Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE(foo);
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="The result_cache hint should be avoided ">
    <div class="paragraph">
      <p>The <code>result\_cache</code> Oracle hint can vastly improve performance, but it comes at the cost of extra memory consumption, so one should double-check that the gain in performance is significant, and avoid overusing this feature in general.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SELECT /*+ result_cache */ * FROM DUAL;  -- Noncompliant
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Compound triggers should define at least two triggers">
    <div class="paragraph">
      <p>Compound triggers were introduced to ease the implementation of multiple triggers which need to work in cooperation.</p>
    </div>

    <div class="paragraph">
      <p>Typically, a \`FOR EACH ROW trigger accumulates facts, and an AFTER STATEMENT trigger performs the actual changes.</p>
    </div>

    <div class="paragraph">
      <p>The compound trigger can hold a state common to all the triggers it defines, thereby removing the need to use package variables. This approach is sometimes the only possible one, as when avoiding a mutating table ORA-04091\` error, or it can be used to get better performance.</p>
    </div>

    <div class="paragraph">
      <p>However, there is no point in defining a compound trigger which contains only a single trigger, since there is no state to be shared. In such cases, a simple trigger should be used instead.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE TRIGGER my_trigger  -- Noncompliant; defines a single trigger
      FOR INSERT ON my_table
      COMPOUND TRIGGER

      AFTER EACH ROW IS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('New row inserted!');
      END AFTER EACH ROW;

      END;
      /
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE TRIGGER my_trigger
      AFTER INSERT
      ON my_table
      FOR EACH ROW
      BEGIN
      DBMS_OUTPUT.PUT_LINE('New row inserted!');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Reserved words should be written in lower case">
    <div class="paragraph">
      <p>All reserved words should be written using the same case to ensure consistency in the code.</p>
    </div>

    <div class="paragraph">
      <p>This rule checks that reserved words are all in lower case.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      begin
      null;
      NULL; -- Noncompliant
      end;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables should not be declared inside loops">
    <div class="paragraph">
      <p>It is a best practice to have variable declarations outside of the loop. Additionally, declaring variables inside a loop is slightly less efficient because memory management is then performed with each iteration of the loop.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      FOR i IN 1..10 
      LOOP
      DECLARE
        variableX NUMBER:= 10;
      BEGIN
          variableX:= variableX+i;
          dbms_output.put_line(variableX);
      END;
      END LOOP;
      END;
      ```

      ```plsql Fix theme={null}
      DECLARE
      variableX NUMBER:= 0;
      BEGIN
      FOR i IN 1..10
      LOOP
      variableX:= variableX+1;
      dbms_output.put_line(variableX);
      END LOOP;
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="%TYPE and %ROWTYPE should not be used in package specification">
    <div class="paragraph">
      <p>\`%TYPE and %ROWTYPE seem like an easy way to make sure that a variable’s type always matches the type of the relevant column in an existing table. If the column type changes, then the variable changes with it.</p>
    </div>

    <div class="paragraph">
      <p>However, Oracle Forms compiled against a procedure using either of these two symbols won’t get the benefit of that flexibility. Instead, at compile time, the relevant type is looked up from the underlying database and used in the form. If the column type changes later or the form is running against a database with different length semantics, attempting to use the form results in an "ORA-04062: Signature of package has been changed" error on the package in question. And the form needs to be recompiled on exactly the same database environment where it will run to avoid the error.</p>
    </div>

    <div class="paragraph">
      <p>Note that %TYPE and %ROWTYPE\` can be used in a package’s private procedures and functions, private package variables, and local variables without issue, but not in the package specification.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE PACKAGE PACK IS
      TYPE mytype IS RECORD (
      var1 mytable.mycolumn%TYPE -- Noncompliant
      );

      FUNCTION MY_FUNC(param1 IN mytable.mycolumn%TYPE) RETURN VARCHAR2; -- Noncompliant

      FUNCTION MY_FUNC2(param1 IN mytable%ROWTYPE) RETURN VARCHAR2; -- Noncompliant

      END;
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE PACKAGE PACK IS
      TYPE mytype IS RECORD (
      var1 VARCHAR2(100) -- Compliant
      );

      FUNCTION MY_FUNC(param1 IN VARCHAR2) RETURN VARCHAR2; -- Compliant

      TYPE myrowtype IS RECORD (
      col1 NUMBER,
      col2 VARCHAR2(30)
      );
      FUNCTION MY_FUNC2(param1 IN myrowtype) RETURN VARCHAR2; -- Compliant

      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Nested blocks should be labeled">
    <div class="paragraph">
      <p>Labeled blocks are useful, especially when the code is badly indented, to help maintainers match the beginning and ending of each block. When blocks are nested, labeling them can improve the code’s readability. This rule detects nested block which do not have a start label.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN -- Compliant, this is not a nested block
      NULL;
      END;
      /

      BEGIN
      BEGIN -- Noncompliant; this nested block has no label
      NULL;
      END;
      END;
      /

      BEGIN
      BEGIN -- Noncompliant; this nested block has only an end label
      NULL;
      END myBlockLabel1;

      <<myBlockLabel2>> -- Compliant
      BEGIN
      NULL;
      END;
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN 
      NULL;
      END;
      /

      BEGIN
      BEGIN myBlockLabel0
      NULL;
      END myBlockLabel0;
      END;
      /

      BEGIN
      BEGIN myBlockLabel1
      NULL;
      END myBlockLabel1;

      <<myBlockLabel2>>
      BEGIN
      NULL;
      END;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Cursors should follow a naming convention">
    <div class="paragraph">
      <p>Shared coding conventions allow teams to collaborate efficiently. This rule checks that all cursor names match the provided regular expression.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TABLE employee(
      name VARCHAR2(42)
      );

      DECLARE
      CURSOR myCursor_ RETURN employee%ROWTYPE; -- Noncompliant

      CURSOR myCursor_ RETURN employee%ROWTYPE IS SELECT * FROM employee; -- Noncompliant
      BEGIN
      NULL;
      END;
      /

      DROP TABLE employee;
      ```

      ```plsql Fix theme={null}
      CREATE TABLE employee(
      name VARCHAR2(42)
      );

      DECLARE
      CURSOR myCursor RETURN employee%ROWTYPE;

      CURSOR myCursor RETURN employee%ROWTYPE IS SELECT * FROM employee;
      BEGIN
      NULL;
      END;
      /

      DROP TABLE employee;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="COMMIT and ROLLBACK should not be called from non-autonomous transaction triggers">
    <div class="paragraph">
      <p>Calling <code>COMMIT or ROLLBACK from within a trigger will lead to an ORA-04092</code> exception, unless the trigger has its own autonomous transaction.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE accounts(
      balance NUMBER
      );

      INSERT INTO accounts VALUES(0);

      CREATE TABLE log(
      message VARCHAR2(100)
      );

      CREATE TRIGGER beforeLogger
      BEFORE UPDATE ON accounts
      FOR EACH ROW
      BEGIN
      INSERT INTO log VALUES('Attempt to update the value from ' || :OLD.balance || ' to ' || :NEW.balance);
      COMMIT; -- Noncompliant, will fail with a ORA-04092
      END;
      /

      -- We want to be able to log any attempt to update the "accounts" table
      BEGIN
      UPDATE accounts SET balance = 100;
      ROLLBACK; -- Ultimately, this update is rolled back, however we still want to log it
      END;
      /

      SELECT * FROM log;

      DROP TRIGGER beforeLogger;

      DROP TABLE log;

      DROP TABLE accounts;
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE accounts(
      balance NUMBER
      );

      INSERT INTO accounts VALUES(0);

      CREATE TABLE log(
      message VARCHAR2(100)
      );

      CREATE TRIGGER beforeLogger
      BEFORE UPDATE ON accounts
      FOR EACH ROW
      DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      BEGIN
      INSERT INTO log VALUES('Attempt to update the value from ' || :OLD.balance || ' to ' || :NEW.balance);
      COMMIT; -- Compliant, commits the trigger's autonomous transaction, not the main one
      END;
      /

      -- We want to be able to log any attempt to update the "accounts" table
      BEGIN
      UPDATE accounts SET balance = 100;
      ROLLBACK; -- Ultimately, this update is rolled back, however we still want to log it
      END;
      /

      SELECT * FROM log;

      DROP TRIGGER beforeLogger;

      DROP TABLE log;

      DROP TABLE accounts;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables should be nullable">
    <div class="paragraph">
      <p>Declaring a variable with the NOT NULL constraint incurs a small performance cost - while this constraint may not really be required. Using such a constraint should be avoided.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      counter PLS_INTEGER NOT NULL := 0; -- Noncompliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      counter PLS_INTEGER := 0; -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FETCH ... BULK COLLECT INTO should not be used without a LIMIT clause">
    <div class="paragraph">
      <p>A <code>FETCH ... BULK COLLECT INTO without a LIMIT clause will load all the records returned by the cursor at once. This may lead to memory exhaustion. Instead, it is better to process the records in chunks using the LIMIT</code> clause.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      -- Fetches all records at once, requiring lots of memory
      DECLARE
      TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
      largeTableRowArray largeTableRowArrayType;
      CURSOR myCursor IS SELECT * FROM largeTable;
      BEGIN
      OPEN myCursor;

      FETCH myCursor BULK COLLECT INTO largeTableRowArray; -- Non-compliant

      DBMS_OUTPUT.PUT_LINE('Alternative 1: ' || largeTableRowArray.COUNT || ' records');

      CLOSE myCursor;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      -- fetches one chunk at a time, requiring constant memory
      DECLARE
      TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
      largeTableRowArray largeTableRowArrayType;
      CURSOR myCursor IS SELECT * FROM largeTable;
      counter PLS_INTEGER := 0;
      BEGIN
      OPEN myCursor;

      LOOP
      FETCH myCursor BULK COLLECT INTO largeTableRowArray LIMIT 1000; -- Compliant

      counter := counter + largeTableRowArray.COUNT;

      EXIT WHEN myCursor%NOTFOUND;
      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Alternative 1: ' || counter || ' records');

      CLOSE myCursor;
      END;
      /

      DROP TABLE largeTable;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="DBMS_UTILITY.FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE should be used together">
    <div class="paragraph">
      <p>Since Oracle 10g, <code>DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE is available to get an exception’s stack trace, i.e. files and lines that lead up to the exception. When combined with DBMS\_UTILITY.FORMAT\_ERROR\_STACK</code>, which contains the exception error code and message, developers are able quickly identify defects.</p>
    </div>

    <div class="paragraph">
      <p>This rule verifies that whenever either is used in an exception handler, the other is used as well.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      RAISE_APPLICATION_ERROR(-20000, 'This is an error example');
      EXCEPTION
      WHEN OTHERS THEN  -- Noncompliant; only FORMAT_ERROR_STACK is used
      DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_STACK);           -- "ORA-20000: This is an error example"
      DBMS_OUTPUT.PUT_LINE('');
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      RAISE_APPLICATION_ERROR(-20000, 'This is an error example');
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_STACK);           -- "ORA-20000: This is an error example"
      DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);       -- "ORA-06512: at line 2"
      DBMS_OUTPUT.PUT_LINE('');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="GOTO statements should not be used">
    <div class="paragraph">
      <p>A <code>GOTO</code> statement is an unstructured change in the control flow. They should be avoided and replaced by structured constructs.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      i PLS_INTEGER := 42;
      BEGIN
      IF i < 0 THEN
      GOTO negative; -- Noncompliant
      END IF;

      DBMS_OUTPUT.PUT_LINE('positive');
      goto cleanup; -- Noncompliant

      <<negative>>
      DBMS_OUTPUT.PUT_LINE('negative!');

      <<cleanup>>
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      i PLS_INTEGER := 42;
      BEGIN
      IF i < 0 THEN
      DBMS_OUTPUT.PUT_LINE('negative!'); -- Compliant
      ELSE
      DBMS_OUTPUT.PUT_LINE('positive');
      END IF;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Scale should not be specified for float types">
    <div class="paragraph">
      <p>Float data types, such as <code>FLOAT, DOUBLE PRECISION, and REAL cannot have a scale constraint. Trying to specify a scale results in the exception PLS-00510: Float cannot have scale being raised.</code></p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo FLOAT(10, 3); -- Noncompliant - raises PLS-00510
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo FLOAT(10); -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Reserved words should be written in upper case">
    <div class="paragraph">
      <p>Shared coding conventions allow teams to collaborate efficiently. This rule checks that reserved words are written in upper case.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      null; -- Noncompliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FORALL statements should use the SAVE EXCEPTIONS clause">
    <div class="paragraph">
      <p>When the <code>FORALL statement is used without the SAVE EXCEPTIONS clause and an exception is raised by a DML query, the whole operation is rolled back and the exception goes unhandled. Instead of relying on this default behavior, it is better to always use the SAVE EXCEPTIONS clause and explicitly handle exceptions in a ORA-24381</code> handler.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TABLE my_table(
      id NUMBER(10) NOT NULL
      );

      DECLARE
      TYPE my_table_id_type IS TABLE OF my_table.id%TYPE;
      my_table_ids my_table_id_type := my_table_id_type();
      BEGIN
      FOR i IN 1 .. 10 LOOP
      my_table_ids.EXTEND;
      my_table_ids(my_table_ids.LAST) := i;
      END LOOP;

      -- Cause the failure
      my_table_ids(10) := NULL;

      FORALL i IN my_table_ids.FIRST .. my_table_ids.LAST  -- Noncompliant
      INSERT INTO my_table
      VALUES (my_table_ids(i));
      END;
      /

      SELECT COUNT(*) FROM my_table;

      DROP TABLE my_table;
      ```

      ```plsql Fix theme={null}
      -- ...

      DECLARE
      TYPE my_table_id_type IS TABLE OF my_table.id%TYPE;
      my_table_ids my_table_id_type := my_table_id_type();

      bulk_errors EXCEPTION;
      PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
      BEGIN
      FOR i IN 1 .. 10 LOOP
      my_table_ids.EXTEND;
      my_table_ids(my_table_ids.LAST) := i;
      END LOOP;

      -- Cause the failure
      my_table_ids(10) := NULL;

      FORALL i IN my_table_ids.FIRST .. my_table_ids.LAST SAVE EXCEPTIONS
      INSERT INTO my_table
      VALUES (my_table_ids(i));
      EXCEPTION
      WHEN bulk_errors THEN
      -- Explicitly rollback the whole transaction,
      -- or handle each exception individually by looping over SQL%BULK_EXCEPTIONS
      ROLLBACK;
      END;
      /

      -- ...
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="ROWID and UROWID data types should not be used">
    <div class="paragraph">
      <p>Be careful about your use of Oracle-specific data types like \`ROWID and UROWID. They might offer a slight improvement in performance over other means of identifying a single row (primary key or unique index value), but that is by no means guaranteed.</p>
    </div>

    <div class="paragraph">
      <p>On the other hand, the use of ROWID or UROWID\` means that your SQL statement will not be portable to other SQL databases. Further, many developers are not familiar with these data types, which can make the code harder to maintain.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      id rowid; -- Non-Compliant
      universeId urowid; -- Non-Compliant
      BEGIN
      SELECT rowid INTO id FROM DUAL;
      SELECT rowid INTO universeId FROM DUAL;

      DBMS_OUTPUT.PUT_LINE('id = ' || id);
      DBMS_OUTPUT.PUT_LINE('universe id = ' || universeId);
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Weak REF CURSOR types should not be used">
    <div class="paragraph">
      <p>Weak \`REF CURSOR types are harder to work with than ones with a return type. Indeed, the compiler’s type-checker is unable to make some verifications, which are then delayed till runtime.</p>
    </div>

    <div class="paragraph">
      <p>When the use of weak REF CURSOR is required, it is best to use the SYS\_REFCURSOR built-in type instead of defining a new one.</p>
    </div>

    <div class="paragraph">
      <p>This rule’s sysRefCursorAllowed parameter can be used to control whether or not the usage of SYS\_REFCURSOR\` is allowed.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      TYPE dualCursorType IS REF CURSOR;                      -- Noncompliant
      dualCursor dualCursorType;

      otherCursor SYS_REFCURSOR;                              -- Compliant or non-compliant, depending on the "sysRefCursorAllowed" parameter
      BEGIN
      otherCursor := dualCursor;                              -- Works
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      TYPE dualCursorType IS REF CURSOR RETURN DUAL%ROWTYPE;
      dualCursor dualCursorType;

      TYPE otherCursorType IS REF CURSOR RETURN a%ROWTYPE;
      otherCursor otherCursorType;
      BEGIN
      otherCursor := dualCursor;                              -- raises PLS-00382: expression is of wrong type, which makes debugging easier
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Blocks containing EXECUTE IMMEDIATE should trap all exceptions">
    <div class="paragraph">
      <p>Since the purpose of the <code>EXECUTE IMMEDIATE</code> statement is to execute dynamic SQL queries - which by definition can contain unexpected errors - properly handling exceptions becomes critical. Therefore, care should be taken to trap all possible exceptions.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      result      VARCHAR2(42);
      column      VARCHAR2(42);
      BEGIN
      column := 'DUMMY_2';
      EXECUTE IMMEDIATE 'SELECT ' || column || ' FROM DUAL' INTO result; -- Non-Compliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      result      VARCHAR2(42);
      column      VARCHAR2(42);
      BEGIN
      column := 'DUMMY_2';
      EXECUTE IMMEDIATE 'SELECT ' || column || ' FROM DUAL' INTO result; -- Compliant
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Execute immediate error: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Strings should only be moved to variables or columns which are large enough to hold them">
    <div class="paragraph">
      <p>Trying to assign a large character value to a smaller variable or column will raise an error.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      create table persons (id number, name varchar2(4));

      insert into persons (id, name) values (1, 'Alice'); -- Noncompliant, raises ORA-12899

      create or replace procedure sp1
      is 
      foo varchar2(2);
      begin
      select name into foo from persons where id = 1; -- Noncompliant, may raise ORA-06502
      end;
      ```

      ```plsql Fix theme={null}
      create table persons (id number, name varchar2(8));

      insert into persons (id, name) values (1, 'Alice');

      create or replace procedure sp1
      is 
      foo varchar2(8);
      begin
      select name into foo from persons where id = 1;
      end;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="WHEN clauses should not have too many lines">
    <div class="paragraph">
      <p>The <code>CASE statement should be used only to clearly define some new branches in the control flow. As soon as a WHEN clause contains too many statements this highly decreases the readability of the overall control flow statement. In such case, the content of WHEN</code> clause should be extracted in a dedicated function.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CASE my_variable
      WHEN 0 THEN -- 6 lines till next WHEN
      procedure1;
      procedure2;
      procedure3;
      procedure4;
      procedure5;
      WHEN 1 THEN
      -- ...
      END CASE;
      ```

      ```plsql Fix theme={null}
      DECLARE
      PROCEDURE do_something AS
      BEGIN
      procedure1;
      procedure2;
      procedure3;
      procedure4;
      procedure5;
      END;
      BEGIN
      CASE my_variable
      WHEN 0 THEN
        do_something;
      WHEN 1 THEN
      -- ...
      END CASE;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Single-line comments should not be used">
    <div class="paragraph">
      <p>From the Oracle docs:</p>
    </div>

    <div class="quoteblock">
      <blockquote>
        <div class="paragraph">
          <p>Do not put a single-line comment in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program. The Oracle Precompiler program ignores end-of-line characters, which means that a single-line comment will end at the end of the block.</p>
        </div>
      </blockquote>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      howmany     NUMBER;
      num_tables  NUMBER;
      BEGIN
      -- Begin processing
      SELECT COUNT(*) INTO howmany
      FROM USER_OBJECTS
        WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
      num_tables := howmany;          -- Compute some other value
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      howmany     NUMBER;
      num_tables  NUMBER;
      BEGIN
      /* Begin processing */
      SELECT COUNT(*) INTO howmany
      FROM USER_OBJECTS
        WHERE OBJECT_TYPE = 'TABLE'; 
      num_tables := howmany;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="All branches in a conditional structure should not have exactly the same implementation">
    <div class="paragraph">
      <p>Having all branches of a CASE or IF/ELSIF chain with the same implementation indicates a problem.</p>
    </div>

    <div class="paragraph">
      <p>In the following code:</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      IF param = 1 THEN
      result := 'A';
      ELSIF param = 2 THEN
      result := 'A';
      ELSE
      result := 'A';
      END IF;

      result := CASE param
      WHEN 1 THEN 'A'
      WHEN 2 THEN 'A'
      ELSE 'A'
      END;
      ```

      ```plsql Fix theme={null}
      IF param = 1 THEN   -- no issue, this could have been done on purpose to make the code more readable
      result := 'A';
      ELSIF param = 2 THEN
      result := 'A';
      END IF;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables should not be initialized with NULL">
    <div class="paragraph">
      <p>Explicit variable initializations with null values are superfluous, since unassigned variables are implicitly initialized to null.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo PLS_INTEGER := NULL; -- Noncompliant, the null assignation is superfluous
      bar VARCHAR2(100) := ''; -- Noncompliant, the null assignation is superfluous
      correctInitializedString VARCHAR2(100) := 'Hello world!';

      BEGIN
      IF foo IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('foo is NULL');
      ELSE
      DBMS_OUTPUT.PUT_LINE('foo is NOT NULL');
      END IF;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo PLS_INTEGER;
      bar VARCHAR2(100);
      correctInitializedString VARCHAR2(100) := 'Hello world!';

      BEGIN
      IF foo IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('foo is NULL');
      ELSE
      DBMS_OUTPUT.PUT_LINE('foo is NOT NULL');
      END IF;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Block labels should appear on the same lines as END ">
    <div class="paragraph">
      <p>Labeled blocks are useful to help maintainers match-up the beginning and ending of each section of code, especially when that code is badly indented. However, if used, those labels must appear on the same line as the "END" keyword in order to avoid confusion. Otherwise, the label might be misread by maintainers as a procedure call.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE foo AS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('foo was called!');
      END;
      BEGIN
      BEGIN
      NULL;
      END -- Semicolon was forgotten?

      foo; -- Noncompliant; looks like a procedure call, but is actually END block label

      <<myBlockLabel>>
      BEGIN
      NULL;
      END 
      myBlockLabel; -- Noncompliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      PROCEDURE foo AS
      BEGIN
      DBMS_OUTPUT.PUT_LINE('foo was called!');
      END;
      BEGIN
      BEGIN
      NULL;
      END; 

      foo; -- The method "foo" was actually meant to be called

      <<myBlockLabel>>
      BEGIN
      NULL;
      END myBlockLabel;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="VARCHAR2 should be used">
    <div class="paragraph">
      <p>Currently, VARCHAR and VARCHAR2 are identical data types. But to prevent future changes in behavior, Oracle recommends the use of VARCHAR2.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      var VARCHAR(42);  -- Noncompliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      var VARCHAR2(42);  -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="NCHAR and NVARCHAR2 size should not be specified in bytes">
    <div class="paragraph">
      <p><code>NCHAR and NVARCHAR2 lengths must be given in characters, not bytes. This is partly because a single character may occupy more than a single byte in memory. Specify the field length in bytes, and theoretically your value could overrun the field, but instead Oracle simply refuses to run the code. Specify it in characters, and Oracle will allocate the appropriate number of bytes to store the requested number of characters. Trying to specify the length semantics in bytes will result in the PLS-00639: NCHAR/NVARCHAR2 cannot be byte length semantics</code> exception being raised.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo NCHAR(42 BYTE); -- Noncompliant - raises PLS-00639
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo NCHAR(42);      -- Compliant
      bar NCHAR(42 CHAR); -- Also compliant, as an alternative
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Functions should end with RETURN statements">
    <div class="paragraph">
      <p>Always having a <code>RETURN as the last statement in a function is a good practice as it prevents the ORA-06503 PL/SQL: Function returned without value</code> error.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE FUNCTION incorrectFunction RETURN PLS_INTEGER IS -- Non-Compliant
      BEGIN
      NULL; -- This function was expected to return a PLS_INTEGER, but did not. Will lead to ORA-06503
      END;
      /

      BEGIN
      DBMS_OUTPUT.PUT_LINE('Ret = ' || incorrectFunction2); -- ORA-06503 PL/SQL: Function returned without value
      END;
      /

      DROP FUNCTION incorrectFunction;
      ```

      ```plsql Fix theme={null}
      CREATE FUNCTION correctFunction RETURN PLS_INTEGER IS -- Compliant
      BEGIN
      RETURN 42;
      END;
      /

      DROP FUNCTION correctFunction;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="EXCEPTION_INIT -20,NNN calls should be centralized">
    <div class="paragraph">
      <p>Centralizing the definitions of custom exceptions comes with two major benefits:</p>
    </div>

    <div class="ulist">
      <ul>
        <li>
          <p>The duplication of the exceptions declarations and <code>PRAGMA EXCEPTION\_INIT</code> is avoided</p>
        </li>

        <li>
          <p>The risk of associating multiple different exceptions to the same number is reduced</p>
        </li>
      </ul>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      user_not_found EXCEPTION;
      PRAGMA EXCEPTION_INIT(user_not_found, -20000); -- Noncompliant, user_not_found is bound to -20000
      BEGIN
      NULL;
      END;
      /

      DECLARE
      user_not_found EXCEPTION;
      PRAGMA EXCEPTION_INIT(user_not_found, -20000); -- Noncompliant, user_not_found is again bound to -20000, duplication
      BEGIN
      NULL;
      END;
      /

      DECLARE
      wrong_password EXCEPTION;
      PRAGMA EXCEPTION_INIT(wrong_password, -20000); -- Noncompliant, wrong_password is bound to -20000, conflicting with user_not_found
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE PACKAGE exceptions AS
      user_not_found EXCEPTION;
      wrong_password EXCEPTION;

      PRAGMA EXCEPTION_INIT(user_not_found, -20000); -- Non-Compliant (flag as false-positive)
      PRAGMA EXCEPTION_INIT(wrong_password, -20001); -- Non-Compliant (flag as false-positive), conflicts are easier to avoid
      END;
      /

      DROP PACKAGE exceptions;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="PLS_INTEGER types should be used">
    <div class="paragraph">
      <p>Using a \`NUMBER to store an integer is less performant than using a PLS\_INTEGER. PLS\_INTEGERs require less storage than NUMBERs, and benefit from the use of hardware math, as opposed to the library math required for NUMBERs. Even more performant is the SIMPLE\_INTEGER subtype of PLS\_INTEGER. However, changing to either of these types is only appropriate under certain circumstances.</p>
    </div>

    <div class="paragraph">
      <p>PLS\_INTEGER is only a candidate for NUMBER with a scale of up to 9.</p>
    </div>

    <div class="paragraph">
      <p>SIMPLE\_INTEGER has the same size limitation, in addition to it’s NOT NULL constraint and lack of overflow checking.</p>
    </div>

    <div class="paragraph">
      <p>This rule raises an issue when a NUMBER\` is declared with a scale of 9 or less.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      son NUMBER(1);      -- Noncompliant
      rumbo NUMBER(9);  -- Noncompliant
      conga Number(10);   -- Ignored; falls outside the PLS_INTEGER range
      compalsa PLS_INTEGER;
      ```

      ```plsql Fix theme={null}
      DECLARE
      son SIMPLE_INTEGER;
      rumbo PLS_INTEGER;
      conga Number(10);   -- Ignored; falls outside the PLS_INTEGER range
      compalsa PLS_INTEGER;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="LOOP ... END LOOP; constructs should be avoided">
    <div class="paragraph">
      <p>Simple loops, of the form <code>LOOP ... END LOOP</code>, behave by default as infinite ones, since they do not have a loop condition. They can often be replaced by other, safer, loop constructs.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      i PLS_INTEGER;
      BEGIN
      i := 1;
      LOOP -- Noncompliant, an infinite loop by default and therefore dangerous
      DBMS_OUTPUT.PUT_LINE('First loop i: ' || i);

      i := i + 1;
      EXIT WHEN i > 10;
      END LOOP;

      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      i PLS_INTEGER;
      BEGIN
      FOR i IN 1..10 LOOP -- Compliant, much safer equivalent alternative
      DBMS_OUTPUT.PUT_LINE('Second loop i: ' || i);
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="END; statements should be labeled">
    <div class="paragraph">
      <p>Labels are useful to match the begin and end of each PACKAGE, PROCEDURE or FUNCTION, especially when the code is badly indented or too much nested.</p>
    </div>

    <div class="paragraph">
      <p>This rule raised an issue when the END statement of a PACKAGE, PROCEDURE or FUNCTION is having no label matching the name of the corresponding "begin" statement.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE PACKAGE BODY cust_sal AS  

      PROCEDURE find_sal(c_id customers.id%TYPE) IS 
      c_sal customers.salary%TYPE; 
      BEGIN 
        SELECT salary INTO c_sal 
        FROM customers 
        WHERE id = c_id; 
        dbms_output.put_line('Salary: '|| c_sal); 
      END; -- Noncompliant; not a PROCEDURE
      END; -- Noncompliant; not a PACKAGE
      /
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE PACKAGE BODY cust_sal AS  

      PROCEDURE find_sal(c_id customers.id%TYPE) IS 
      c_sal customers.salary%TYPE; 
      BEGIN 
        SELECT salary INTO c_sal 
        FROM customers 
        WHERE id = c_id; 
        dbms_output.put_line('Salary: '|| c_sal); 
      END find_sal; -- Compliant; it's matching the name of the PROCEDURE
      END cust_sal; -- Compliant: it's matching the name of the PACKAGE
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="UNION should be used with caution">
    <div class="paragraph">
      <p>\`UNION is a convenient syntax to combine the results of two or more SQL statements because it helps you cut a complex problem into multiple simple SQL statements. But when it comes to execution, using UNION is debatable.</p>
    </div>

    <div class="paragraph">
      <p>First, it may be possible to fuse two simple SQL statements into a bigger one that will run faster. Second, UNION is significantly less performant compared to UNION ALL because it removes duplicated entries and runS an internal DISTINCT to achieve this.</p>
    </div>

    <div class="paragraph">
      <p>UNION ALL does not remove duplicates and returns all the results from the queries. It performs faster in most cases compared to UNION. Nevertheless, the quantity of data returned by UNION ALL can be significantly larger than with UNION. On a slow network, the performance gain of using UNION ALL instead of UNION can be negated by the time lost in the larger data transfer.</p>
    </div>

    <div class="paragraph">
      <p>This rule raises an issue on each UNION. It’s up to the developer to challenge its use and see if there is a better way to rewrite without UNION\`.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      -- case #1
      SELECT EMAIL FROM EMPLOYEES WHERE COUNTRY = 'FR'
      UNION                           -- Noncompliant
      SELECT EMAIL FROM EMPLOYEES WHERE COUNTRY = 'CH'

      -- case #2
      -- if you care about not having duplicated entries, then UNION is the good choice
      SELECT EMAIL FROM EMPLOYEES 
      UNION                           -- Noncompliant
      SELECT EMAIL FROM CUSTOMERS
      ```

      ```plsql Fix theme={null}
      -- case #1
      SELECT EMAIL FROM EMPLOYEES WHERE COUNTRY = 'FR' OR COUNTRY = 'CH'

      -- case #2
      -- if you don't care about duplicated entries in the results of this UNION, then UNION ALL should be preferred
      SELECT EMAIL FROM EMPLOYEES 
      UNION ALL
      SELECT EMAIL FROM CUSTOMERS
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="IF statement conditions should not evaluate unconditionally to TRUE or to FALSE">
    <div class="paragraph">
      <p><code>IF statements with conditions that are always false have the effect of making blocks of code non-functional. This can be useful during debugging, but should not be checked in. IF statements with conditions that are always true are completely redundant, and make the code less readable. In either case, unconditional IF</code> statements should be removed.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      IF TRUE THEN
      do_something;
      END IF;

      IF FALSE THEN
      do_something_else;
      END IF;
      ```

      ```plsql Fix theme={null}
      do_something;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="GOTO should not be used to jump backwards">
    <div class="paragraph">
      <p>Jumping back to a previous statement using <code>GOTO</code> is a way to reimplement loops, which PL/SQL already provides in much more readable forms.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      result PLS_INTEGER := 0;
      counter PLS_INTEGER := 1;
      BEGIN
      <<loop>>
      result := result + counter;
      counter := counter + 1;

      IF counter <= 9 THEN
      GOTO loop;                    -- Noncompliant
      END IF;

      DBMS_OUTPUT.PUT_LINE('Sum from 1 to 9 is ' || result); -- Displays 1 + 2 + ... + 8 + 9 = 45
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      result PLS_INTEGER := 0;
      BEGIN
      FOR counter IN 1 .. 9 LOOP
      result := result + counter;
      END LOOP;

      DBMS_OUTPUT.PUT_LINE('Sum from 1 to 9 is ' || result); -- Displays 1 + 2 + ... + 8 + 9 = 45
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="RAISE_APPLICATION_ERROR should only be used with error codes from -20,000 to -20,999">
    <div class="paragraph">
      <p><code>RAISE\_APPLICATION\_ERROR may only be called with an error code from -20,000 to -20,999, which is the range reserved for application errors. When called with another value, Oracle raises the exception: ORA-21000: error number argument to raise\_application\_error of 0 is out of range.</code></p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      RAISE_APPLICATION_ERROR(0, 'This is an application error'); -- Non-Compliant - raises ORA-21000
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      RAISE_APPLICATION_ERROR(-20000, 'This is an application error'); -- Compliant
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="MLSLABEL should not be used">
    <div class="paragraph">
      <p>The deprecated MLSLABEL datatype is still supported only for backwards compatibility with Trusted Oracle, and since Oracle8, the only valid value it can hold is NULL. Thus, the usage of this type should be progressively removed.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      foo MLSLABEL; -- Noncompliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="VARCHAR2 and NVARCHAR2 should be used">
    <div class="paragraph">
      <p>For fixed-length values, a \`CHAR field occupies the same amount of disk space as a VARCHAR2 field, but for variable-length values CHAR fields use more storage space and make searching more difficult by right-padding values with whitespaces. Therefore VARCHAR2 fields are preferred. Similarly, NCHAR should be replaced by NVARCHAR2.</p>
    </div>

    <div class="paragraph">
      <p>Note that for 1-character fields, CHAR is naturally equivalent to VARCHAR2\`, but the latter is still preferred for consistency.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      var1 CHAR; -- Noncompliant

      var2 CHAR(42); -- Noncompliant

      var3 NCHAR; -- Noncompliant

      var4 NCHAR(42); -- Noncompliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      var1 VARCHAR2(42); 

      var2 VARCHAR2(42);

      var3 NVARCHAR2(42); 

      var4 NVARCHAR2(42);
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Labels should not be reused in inner scopes">
    <div class="paragraph">
      <p>Using the same name for multiple purposes reduces the understandability of the code and might eventually lead to bugs.</p>
    </div>

    <div class="paragraph">
      <p>This rule verifies that no label is reused in an inner scope.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      <<foo>>
      DECLARE
      a CONSTANT PLS_INTEGER := 0;
      BEGIN
      <<foo>>                                  -- Noncompliant
      DECLARE
      b CONSTANT PLS_INTEGER := 42;
      BEGIN
      DBMS_OUTPUT.PUT_LINE('x = ' || foo.b); -- Confusing
      END;
      END;
      /
      ```

      ```plsql Fix theme={null}
      <<foo>>
      DECLARE
      a CONSTANT PLS_INTEGER := 0;
      BEGIN
      <<bar>>
      DECLARE
      b CONSTANT PLS_INTEGER := 42;
      BEGIN
      DBMS_OUTPUT.PUT_LINE('x = ' || bar.b);
      END;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="CROSS JOIN queries should not be used">
    <div class="paragraph">
      <p>A <code>CROSS JOIN</code> query will return all records where each row from the first table is combined with each row from the second table. This means that such a query returns the Cartesian product of the sets of rows from the joined tables, which is why it is also know as "Cartesian product query".</p>
    </div>

    <div class="paragraph">
      <p>Such a query can return a huge amount of data, and therefore should be used only with great caution and only when really needed.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      -- Standard ANSI syntax
      SELECT *
      INTO employeeArray
      FROM employee CROSS JOIN department; -- Noncompliant; explicit cross join
      END;
      /

      BEGIN
      -- Old syntax
      SELECT *
      INTO employeeArray
      FROM employee, department; -- Noncompliant; also a cross join
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="RESULT_CACHE should not be used">
    <div class="paragraph">
      <p>Because <code>RESULT\_CACHE</code>-enabled functions increase memory consumption, one should double-check that the gain in performances is significant, and avoid over-using this feature in general.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE FUNCTION myFastFunction RETURN PLS_INTEGER RESULT_CACHE AS -- Noncompliant
      BEGIN
      RETURN 42;
      END;
      /

      DROP FUNCTION myFastFunction;
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FOR loop end conditions should not be hard-coded">
    <div class="paragraph">
      <p>Hard-coding bounds in FOR loops is a bad practice, just as magic numbers in general are. Often, those magic bounds can be replaced by dynamic values. If that is not possible, replacing the literal number with a constant is still better.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      TYPE myCollectionType IS VARRAY(3) OF VARCHAR2(42);
      myCollection myCollectionType := myCollectionType('David', 'John', 'Richard');

      BEGIN

      FOR i IN 2 .. 3 -- Noncompliant; magic numbers used for the loop bounds
      LOOP
      DBMS_OUTPUT.PUT_LINE('name = ' || myCollection(i));
      END LOOP;

      FOR i IN 2 .. myCollection.LAST -- Noncompliant, better but still magic
      LOOP
      DBMS_OUTPUT.PUT_LINE('name = ' || myCollection(i));
      END LOOP;

      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      TYPE myCollectionType IS VARRAY(3) OF VARCHAR2(42);
      myCollection myCollectionType := myCollectionType('David', 'John', 'Richard');

      BEGIN
      FOR i IN myCollection.FIRST .. myCollection.LAST
      LOOP
      DBMS_OUTPUT.PUT_LINE('name = ' || myCollection(i));
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Large item lists should not be used with IN clauses">
    <div class="paragraph">
      <p>Oracle supports at most 1000 items in a SQL query’s <code>IN clause. When more items are given, the exception ORA-01795 maximum number of expressions in a list is 1000 is raised. Thus, IN</code> clauses are not as scalable as joins.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT *
      INTO result
      FROM my_table
      WHERE col1 IN (1, 2, 3, ..., 1001);       -- Noncompliant - raises ORA-01795
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      SELECT my_table.*
      INTO result
      FROM my_table
      JOIN new_in_table
      WHERE my_table.col1 = new_in_table.value; -- Compliant
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FORALL should be used">
    <div class="paragraph">
      <p>The performance of DML queries in loops can be improved by placing them in a <code>FORALL</code> statement. This way, queries will be sent in bulk, minimizing the number of context switches between PL/SQL and SQL.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE largeTable(
      foo VARCHAR2(42)
      );

      BEGIN
      FOR i IN 1 .. 100000 LOOP
      INSERT INTO largeTable VALUES('bar' || i); -- Non-compliant
      END LOOP;
      END;
      /

      SET TIMING ON
      DECLARE
      TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
      largeTableRowArray largeTableRowArrayType;
      BEGIN
      SELECT * BULK COLLECT INTO largeTableRowArray FROM largeTable;

      EXECUTE IMMEDIATE 'TRUNCATE TABLE largeTable';
      FOR i IN largeTableRowArray.FIRST .. largeTableRowArray.LAST LOOP
      INSERT INTO largeTable (foo) VALUES (largeTableRowArray(i).foo); -- Non-compliant
      END LOOP;
      END;
      /
      SET TIMING OFF

      DROP TABLE largeTable;
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      CREATE TABLE largeTable(
      foo VARCHAR2(42)
      );

      BEGIN
      FOR i IN 1 .. 100000 LOOP
      INSERT INTO largeTable VALUES('bar' || i); -- Non-compliant
      END LOOP;
      END;
      /

      SET TIMING ON
      DECLARE
      TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
      largeTableRowArray largeTableRowArrayType;
      BEGIN
      SELECT * BULK COLLECT INTO largeTableRowArray FROM largeTable;

      EXECUTE IMMEDIATE 'TRUNCATE TABLE largeTable';
      FORALL i IN largeTableRowArray.FIRST .. largeTableRowArray.LAST
      INSERT INTO largeTable (foo) VALUES (largeTableRowArray(i).foo); -- Compliant

      INSERT INTO largeTable (foo) VALUES ('baz'); -- Compliant, not in a loop
      END;
      /
      SET TIMING OFF

      DROP TABLE largeTable;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Unused cursor parameters should be removed">
    <div class="paragraph">
      <p>Unused cursor parameters are misleading. Whatever the values passed to such parameters, the behavior will be the same.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      cursor c_list_emp(pp_country varchar2, pp_status varchar2)  is -- Noncompliant pp_status is not used
      select e.employee_code,
            p.first_name,
            p.last_name,
            e.country
      from persons       p,
      join employee_list e on e.person_id = p.person_id
      where e.country = pp_country;
      ```

      ```plsql Fix theme={null}
      cursor c_list_emp(pp_country varchar2, pp_status varchar2)  is
      select e.employee_code,
            p.first_name,
            p.last_name,
            e.country
      from persons       p,
      join employee_list e on e.person_id = p.person_id
      where e.country     = pp_country
       and e.status_code = pp_status; -- use the parameter
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="PACKAGE BODY initialization sections should not contain RETURN statements">
    <div class="paragraph">
      <p>In a CREATE PACKAGE BODY, the purpose of the initialization section is to set the initial values of the package’s global variables. It is therefore surprising to find a RETURN statement there, as all its following statements will be unreachable.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      CREATE OR REPLACE PACKAGE foo AS
      FUNCTION getBar RETURN PLS_INTEGER;
      bar PLS_INTEGER;
      END;
      /

      CREATE OR REPLACE PACKAGE BODY foo AS
      FUNCTION getBar RETURN PLS_INTEGER AS
      BEGIN
      RETURN bar; -- Compliant
      END;
      BEGIN
      bar := 42;
      DBMS_OUTPUT.PUT_LINE('package loaded');
      RETURN; -- Noncompliant
      DBMS_OUTPUT.PUT_LINE('this is unreachable code');
      END;
      /

      DROP PACKAGE BODY foo;

      DROP PACKAGE foo;
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Collections should not be iterated in FOR loops">
    <div class="paragraph">
      <p>The \`FOR loop at first seems like a convenient way of iterating over the elements of a collection, but doing so will raise a VALUE\_ERROR exception if the collection is empty. Looping instead from 1 to COUNT doesn’t work either if the collection is sparse; that leads to a ORA-01403: no data found error.</p>
    </div>

    <div class="paragraph">
      <p>Instead, a WHILE\` loop should be used.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      TYPE fooType IS TABLE OF VARCHAR2(42);
      foo fooType := new fooType('Strawberry', 'Apple', 'Banana');
      BEGIN
      foo.DELETE(2);                                -- The collection is now sparse

      FOR i IN 1 .. foo.COUNT                       -- Noncompliant - leads to ORA-01403: no data found
      LOOP
      DBMS_OUTPUT.PUT_LINE(i || ' = ' || foo(i));
      END LOOP;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      TYPE fooType IS TABLE OF VARCHAR2(42);
      foo fooType := new fooType('Strawberry', 'Apple', 'Banana');
      i PLS_INTEGER;
      BEGIN
      foo.DELETE(2);                                -- The collection is now sparse

      i := foo.FIRST;

      WHILE (i IS NOT NULL)                         -- Compliant - works as expected
      LOOP
      DBMS_OUTPUT.PUT_LINE(i || ' = ' || foo(i));
      i := foo.NEXT(i);
      END LOOP;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="CASE statements should end with ELSE clauses">
    <div class="paragraph">
      <p>The requirement for a final <code>ELSE clause is defensive programming. The CASE</code> statement should always provide a value.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      CASE grade -- Noncompliant, can raise a CASE_NOT_FOUND exception.
      WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
      WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
      END CASE;
      ```

      ```plsql Fix theme={null}
      CASE grade
      WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
      WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
      ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
      END CASE;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Quoted identifiers should not be used">
    <div class="paragraph">
      <p>Quoted identifiers are confusing to many programmers, as they look similar to string literals. Moreover, for maximum portability, identifiers should be self-descriptive and should not contain accents. Quoted identifiers can contain any character, which can be confusing.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      "x + y" PLS_INTEGER := 0; -- Noncompliant, quoted identifiers are confusing
      x PLS_INTEGER := 40;
      y PLS_INTEGER := 2;
      "hello" VARCHAR2(42) := 'world';  -- Noncompliant

      BEGIN
      DBMS_OUTPUT.PUT_LINE("x + y"); -- Noncompliant, displays 0
      DBMS_OUTPUT.PUT_LINE("hello"); -- Noncompliant, confusing, displays "world" and not "hello"
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      my_int PLS_INTEGER := 0;
      x PLS_INTEGER := 40;
      y PLS_INTEGER := 2;
      greeting VARCHAR2(42) := 'hello';
      BEGIN
      DBMS_OUTPUT.PUT_LINE(my_int);
      DBMS_OUTPUT.PUT_LINE(x + y); -- Compliant, displays 42

      DBMS_OUTPUT.PUT_LINE(greeting);
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="FORALL should be used with INDICES OF instead of IN">
    <div class="paragraph">
      <p>Using <code>FORALL i IN x.first ... x.last or FORALL i IN 1 ... x.count might fail when indexed collections are sparse as Oracle tries to access non-existent element(s) of x. FORALL i IN INDICES OF x syntax will always work including sparse collections. Thus using FORALL i IN INDICES OF x</code> should be preferred as it makes code more robust and easier to review.</p>
    </div>

    <div class="paragraph" />

    <CodeGroup>
      ```plsql Bad theme={null}
      FORALL i IN 1 .. l_tab.COUNT  -- Non-Compliant
      INSERT INTO forall_test VALUES l_tab(i);

      FORALL i IN l_tab.first .. l_tab.last  -- Non-Compliant
      INSERT INTO forall_test VALUES l_tab(i);
      ```

      ```plsql Fix theme={null}
      FORALL i IN INDICES OF l_tab
      INSERT INTO forall_test VALUES l_tab(i);
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Boolean checks should not be inverted">
    <div class="paragraph">
      <p>It is needlessly complex to invert the result of a boolean comparison. The opposite comparison should be made instead.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      IF NOT x <> y THEN   -- Noncompliant
      -- ...
      END IF;
      ```

      ```plsql Fix theme={null}
      IF x = y THEN
      -- ...
      END IF;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Lines should not end with trailing whitespaces">
    <div class="paragraph">
      <p>Trailing whitespaces bring no information, they may generate noise when comparing different versions of the same file, and they can create bugs when they appear after a \ marking a line continuation. They should be systematically removed.</p>
    </div>

    <div class="paragraph">
      <p>An automated code formatter allows to completely avoid this family of issues and should be used wherever possible.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      -- The following line has many trailing whitespaces
      foo VARCHAR2(42) := 'a     
      b';
      BEGIN
      -- Will misleadingly show 3, counting only the characters 'a', 'b', and the line terminator, but none of the trailing whitespaces
      DBMS_OUTPUT.PUT_LINE(LENGTH(foo));
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Statements should be on separate lines">
    <div class="paragraph">
      <p>Putting multiple statements on a single line lowers the code readability and makes debugging the code more complex.</p>
    </div>

    <div class="paragraph">
      <p>Unresolved directive in \<stdin> - include::\{noncompliant}\[]</p>
    </div>

    <div class="paragraph">
      <p>Write one statement per line to improve readability.</p>
    </div>

    <div class="paragraph">
      <p>Unresolved directive in \<stdin> - include::\{compliant}\[]</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello '); DBMS_OUTPUT.PUT_LINE('World'); -- Noncompliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello ');
      DBMS_OUTPUT.PUT_LINE('World');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="SQL JOIN conditions should involve all joined tables">
    <div class="paragraph">
      <p>When a SQL query is joining n tables (with n>=2), it is expected to have join conditions defined to determine on which columns these n tables should be joined. At minimum, for n joined tables, the SQL query should contain (n-1) join conditions involving all the joined table to avoid a full cartesian product between the rows of the n tables.</p>
    </div>

    <div class="paragraph">
      <p>Not doing so will imply that too many rows will be returned. If this is not the case and unless this has been done on purpose, the SQL query should be reviewed and missing conditions should be added or useless tables should be removed from the SQL query.</p>
    </div>

    <div class="paragraph">
      <p>This rule is raising no issue when the SQL query is involving <code>CROSS JOIN, NATURAL JOIN</code> statements.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      SELECT c.id, c.name, o.id, o.item_id, o.item_quantity
      FROM ORDERS o, CUSTOMERS c; -- Noncompliant; no JOIN condition at all

      SELECT c.id, c.name, o.id, o.item_id, o.item_quantity
      FROM ORDERS o
      JOIN CUSTOMERS c ON o.customer_id = o.id; -- Noncompliant; no condition related to CUSTOMERS

      SELECT f.name, d.title, l.*
      FROM FOLDERS f, DOCUMENTS d, DOC_LINES l -- Noncompliant; missing at least one condition related to DOC_LINES
      WHERE f.id = d.folder_id;
      ```

      ```plsql Fix theme={null}
      SELECT c.id, c.name, o.id, o.item_id, o.item_quantity
      FROM ORDERS o, CUSTOMERS c
      WHERE o.customer_id = c.id; -- Compliant

      SELECT c.id, c.name, o.id, o.item_id, o.item_quantity
      FROM ORDERS o
      JOIN CUSTOMERS c ON o.customer_id = c.id; -- Compliant

      SELECT f.name, d.title, l.*
      FROM FOLDERS f, DOCUMENTS d, DOC_LINES l
      WHERE f.id = d.folder_id
      AND d.id = l.document_id; -- Compliant
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Exception handlers should preserve the original exceptions">
    <div class="paragraph">
      <p>When handling a caught exception, the original exception’s message and stack trace should be logged or passed forward.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      d VARCHAR2(1);
      BEGIN
      SELECT dummy INTO d FROM DUAL WHERE dummy = 'Y'; -- Will raise NO_DATA_FOUND
      DBMS_OUTPUT.PUT_LINE('d = ' || d);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN -- Noncompliant, did we really want to mask this exception?
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      d VARCHAR2(1);
      BEGIN
      SELECT dummy INTO d FROM DUAL WHERE dummy = 'Y'; -- Will raise NO_DATA_FOUND
      DBMS_OUTPUT.PUT_LINE('d = ' || d);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: No data found');
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="DDL statements should not be used">
    <div class="paragraph">
      <p>Allowing an application to dynamically change the structure of a database at runtime is very dangerous because the application can become unstable under unexpected conditions. Best practices dictate that applications only manipulate data.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TABLE my_table(
      my_column INTEGER
      );
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Magic numbers should not be used">
    <div class="paragraph">
      <p>Magic numbers make the code more complex to understand as it requires the reader to have knowledge about the global context to understand the number itself.
      Their usage may seem obvious when writing the code, but it may not be the case for another developer or later once the context faded away.
      -1, 0, and 1 are not considered magic numbers.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      FOR l_counter IN 1..5 -- Noncompliant, 5 is a magic number
      LOOP
      ...
      END LOOP;
      END;
      ```

      ```plsql Fix theme={null}
      DECLARE
      co_number_of_cycles CONSTANT NUMBER := 5;
      BEGIN
      FOR l_counter IN 1..co_number_of_cycles -- Compliant
      LOOP
      ...
      END LOOP;
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Boolean literals should not be redundant">
    <div class="paragraph">
      <p>A boolean literal can be represented in two different ways: \{true} or \{false}.
      They can be combined with logical operators (\{ops}) to produce logical expressions that represent truth values.
      However, comparing a boolean literal to a variable or expression that evaluates to a boolean value is unnecessary and can make the code harder to read and understand.
      The more complex a boolean expression is, the harder it will be for developers to understand its meaning and expected behavior, and it will favour the introduction of new bugs.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo BOOLEAN := TRUE;
      BEGIN
      IF foo = FALSE THEN                     -- Noncompliant
      DBMS_OUTPUT.PUT_LINE('foo = false!');
      ELSIF foo = TRUE THEN                   -- Noncompliant
      DBMS_OUTPUT.PUT_LINE('foo = true!');
      END IF;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      foo BOOLEAN := TRUE;
      BEGIN
      IF NOT foo THEN                         -- Compliant
      DBMS_OUTPUT.PUT_LINE('foo = false!');
      ELSIF foo THEN                          -- Compliant
      DBMS_OUTPUT.PUT_LINE('foo = true!');
      END IF;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Exceptions should not be ignored">
    <div class="paragraph">
      <p>When exceptions occur, it is usually a bad idea to simply ignore them. Instead, it is better to handle them properly, or at least to log them.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      d VARCHAR2(1);
      BEGIN
      SELECT dummy INTO d FROM DUAL WHERE dummy = 'Y'; -- Will raise NO_DATA_FOUND
      DBMS_OUTPUT.PUT_LINE('d = ' || d);
      EXCEPTION
      WHEN NO_DATA_FOUND THEN -- Noncompliant, did we really want to mask this exception?
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="SQL EXISTS subqueries should not be used ">
    <div class="paragraph">
      <p>SQL queries that use <code>EXISTS subqueries are inefficient because the subquery is re-run for every row in the outer query’s table. There are more efficient ways to write most queries, ways that do not use the EXISTS</code> condition.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      SELECT e.name
      FROM employee e
      WHERE EXISTS (SELECT * FROM department d WHERE e.department_id = d.id AND d.name = 'Marketing');
      ```

      ```plsql Fix theme={null}
      SELECT e.name
      FROM employee e INNER JOIN department d
      ON e.department_id = d.id AND d.name = 'Marketing';
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Mergeable if statements should be combined">
    <div class="paragraph">
      <p>Nested code - blocks of code inside blocks of code - is eventually necessary, but increases complexity. This is why keeping the code as flat as possible, by avoiding unnecessary nesting, is considered a good practice.</p>
    </div>

    <div class="paragraph">
      <p>Merging if statements when possible will decrease the nesting of the code and improve its readability.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      IF something THEN
      IF something_else THEN             -- Noncompliant
      -- ...
      END IF;
      END IF;
      ```

      ```plsql Fix theme={null}
      IF something AND something_else THEN -- Compliant
      -- ...
      END IF;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Track lack of copyright and license headers">
    <div class="paragraph">
      <p>Each source file should start with a header stating file ownership and the license which must be used to distribute the application.</p>
    </div>

    <div class="paragraph">
      <p>This rule must be fed with the header text that is expected at the beginning of every file.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      --
      -- SonarQube, open source software quality management tool.
      -- Copyright (C) 2008-2018 SonarSource
      -- mailto:contact AT sonarsource DOT com
      --
      -- SonarQube is free software; you can redistribute it and/or
      -- modify it under the terms of the GNU Lesser General Public
      -- License as published by the Free Software Foundation; either
      -- version 3 of the License, or (at your option) any later version.
      --
      -- SonarQube is distributed in the hope that it will be useful,
      -- but WITHOUT ANY WARRANTY; without even the implied warranty of
      -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
      -- Lesser General Public License for more details.
      --
      -- You should have received a copy of the GNU Lesser General Public License
      -- along with this program; if not, write to the Free Software Foundation,
      -- Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
      --
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Redundant pairs of parentheses should be removed">
    <div class="paragraph">
      <p>The use of parentheses, even those not required to enforce a desired order of operations, can clarify the intent behind a piece of code. However, redundant pairs of parentheses could be misleading and should be removed.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      x := (y / 2 + 1); -- Compliant even if the parentheses are ignored
      IF (x > 0) AND ((x+y > 0)) THEN -- Noncompliant
      -- ...
      END IF;
      ```

      ```plsql Fix theme={null}
      x := (y / 2 + 1);
      IF (x > 0) AND (x+y > 0) THEN
      -- ...
      END IF;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="DELETE and UPDATE statements should contain WHERE clauses">
    <div class="paragraph">
      <p><code>UPDATE and DELETE statements should contain WHERE</code> clauses to keep the modification of records under control. Otherwise unexpected data loss could result.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      maxAge PLS_INTEGER := 60;
      BEGIN
      UPDATE employee SET status = 'retired'; -- Noncompliant - the WHERE was forgotten
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      maxAge PLS_INTEGER := 60;
      BEGIN
      UPDATE employee SET status = 'retired' WHERE age > maxAge;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Unused procedure and function parameters should be removed">
    <div class="paragraph">
      <p>A typical code smell known as unused function parameters refers to parameters declared in a function but not used anywhere within the function’s body.
      While this might seem harmless at first glance, it can lead to confusion and potential errors in your code.
      Disregarding the values passed to such parameters, the function’s behavior will be the same, but the programmer’s intention won’t be clearly expressed anymore.
      Therefore, removing function parameters that are not being utilized is considered best practice.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE PROCEDURE say_hello(name VARCHAR2) AS -- Noncompliant; name is not used
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello World');
      END;
      /
      ```

      ```plsql Fix theme={null}
      CREATE PROCEDURE say_hello(name VARCHAR2) AS -- Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello ' || name);
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Package names should comply with a naming convention">
    <div class="paragraph">
      <p>Shared naming conventions improve readability and allow teams to collaborate efficiently.
      This rule checks that all package names match a provided regular expression.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE PACKAGE invalid_package_ AS
      PROCEDURE display_message;
      END invalid_package_;
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE PACKAGE valid_package AS
      PROCEDURE display_message;
      END valid_package;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables and columns should not be self-assigned">
    <div class="paragraph">
      <p>There is no reason to re-assign a variable to itself. Either this statement is redundant and should be removed, or the re-assignment is a mistake and some other value or variable was intended for the assignment instead.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      UPDATE person
      SET name = name;
      ```

      ```plsql Fix theme={null}
      UPDATE person
      SET name = UPPER(name);
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Dynamically executing code is security-sensitive">
    <div class="paragraph">
      <p>Executing code dynamically is security-sensitive. It has led in the past to the following vulnerabilities:</p>
    </div>

    <div class="ulist">
      <ul>
        <li>
          <p><a href="http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2017-9807">CVE-2017-9807</a></p>
        </li>

        <li>
          <p><a href="http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2017-9802">CVE-2017-9802</a></p>
        </li>
      </ul>
    </div>

    <div class="paragraph">
      <p>Some APIs enable the execution of dynamic code by providing it as strings at runtime. These APIs might be useful in some very specific meta-programming use-cases. However most of the time their use is frowned upon because they also increase the risk of maliciously <a href="https://owasp.org/www-community/attacks/Code_Injection">Injected Code</a>. Such attacks can either run on the server or in the client (example: XSS attack) and have a huge impact on an application’s security.</p>
    </div>

    <div class="paragraph">
      <p>This rule marks for review each occurrence of such dynamic code execution. This rule does not detect code injections. It only highlights the use of APIs which should be used sparingly and very carefully.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE PROCEDURE ckpwd_bind (p_user IN VARCHAR2, p_pass IN VARCHAR2) 
      IS
      v_query  VARCHAR2(100);
      v_output NUMBER;
      BEGIN
      v_query := 
      q'{SELECT COUNT(*) FROM user_pwd WHERE username = :1 AND password = :2}';
      EXECUTE IMMEDIATE v_query 
      INTO v_output
      USING p_user, p_pass;
      END;
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="String literals should not be duplicated">
    <div class="paragraph">
      <p>Duplicated string literals make the process of refactoring complex and error-prone, as any change would need to be propagated on all occurrences.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      prepare('action1');
      execute('action1');
      release('action1');
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      co_action CONSTANT VARCHAR2(7) := 'action1';
      BEGIN
      prepare(co_action);
      execute(co_action);
      release(co_action);
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Columns to be read with a SELECT statement should be clearly defined">
    <div class="paragraph">
      <p><code>SELECT \*</code> should be avoided because it releases control of the returned columns and could therefore lead to errors and potentially to performance issues.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      myvar CHAR;
      BEGIN
      SELECT * INTO myvar FROM DUAL; -- Noncompliant
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      myvar CHAR;
      BEGIN
      SELECT dummy INTO myvar FROM DUAL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Neither DES (Data Encryption Standard) nor DESede (3DES) should be used">
    <div class="paragraph">
      <p>According to the US National Institute of Standards and Technology (NIST), the Data Encryption Standard (DES) is no longer considered secure:</p>
    </div>

    <div class="quoteblock">
      <blockquote>
        <div class="paragraph">
          <p>Adopted in 1977 for federal agencies to use in protecting sensitive, unclassified information, the DES is being withdrawn because it no longer provides the security that is needed to protect federal government information.</p>
        </div>

        <div class="paragraph">
          <p>Federal agencies are encouraged to use the Advanced Encryption Standard, a faster and stronger algorithm approved as FIPS 197 in 2001.</p>
        </div>
      </blockquote>
    </div>

    <div class="paragraph">
      <p>For similar reasons, RC2 should also be avoided.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
                             + DBMS_CRYPTO.CHAIN_CBC
                             + DBMS_CRYPTO.PAD_PKCS5;
      ```

      ```plsql Fix theme={null}
      PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
                             + DBMS_CRYPTO.CHAIN_CBC
                             + DBMS_CRYPTO.PAD_PKCS5;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Output parameters should be assigned">
    <div class="paragraph">
      <p>Marking a parameter for output means that callers will expect its value to be updated with a result from the execution of the procedure. Failing to update the parameter before the procedure returns is surely an error.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE OR REPLACE PROCEDURE greet(
      name     IN  VARCHAR2,
      greeting OUT VARCHAR2) -- Noncompliant
      AS
      message VARCHAR2(45);
      BEGIN
      SELECT 'Hello ' || RTRIM(name) INTO message FROM DUAL;
      END;
      ```

      ```plsql Fix theme={null}
      CREATE OR REPLACE PROCEDURE greet(
      name     IN  VARCHAR2,
      greeting OUT VARCHAR2)
      AS
      BEGIN
      SELECT 'Hello ' || RTRIM(name) INTO greeting FROM DUAL;
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="SHA-1 and Message-Digest hash algorithms should not be used in secure contexts">
    <div class="paragraph">
      <p>The MD5 algorithm and its successor, SHA-1, are no longer considered secure, because it is too easy to create hash collisions with them. That is, it takes too little computational effort to come up with a different input that produces the same MD5 or SHA-1 hash, and using the new, same-hash value gives an attacker the same access as if he had the originally-hashed value. This applies as well to the other Message-Digest algorithms: MD2, MD4, MD6, HAVAL-128, HMAC-MD5, DSA (which uses SHA-1), RIPEMD, RIPEMD-128, RIPEMD-160, HMACRIPEMD160.</p>
    </div>

    <div class="paragraph">
      <p>Consider using safer alternatives, such as SHA-256, SHA-512 or SHA-3.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      DBMS_CRYPTO.Hash(str, HASH_MD4);

      DBMS_CRYPTO.Hash(str, HASH_MD5);

      DBMS_CRYPTO.Hash(str, HASH_SH1);
      ```

      ```plsql Fix theme={null}
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Comments should not be located at the end of lines of code">
    <div class="paragraph">
      <p>This rule verifies that single-line comments are not located at the ends of lines of code. The main idea behind this rule is that in order to be really readable, trailing comments would have to be properly written and formatted (correct alignment, no interference with the visual structure of the code, not too long to be visible) but most often, automatic code formatters would not handle this correctly: the code would end up less readable. Comments are far better placed on the previous empty line of code, where they will always be visible and properly formatted.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      a := b + c; -- This is a trailing comment that can be very very long
      ```

      ```plsql Fix theme={null}
      -- This very long comment is better placed before the line of code
      a := b + c;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Function and procedure names should comply with a naming convention">
    <div class="paragraph">
      <p>Shared coding conventions allow teams to collaborate efficiently. This rule checks that all function names match a provided regular expression.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE FUNCTION my_function_ RETURN PLS_INTEGER AS -- Noncompliant
      BEGIN
      RETURN 42;
      END;
      /
      ```

      ```plsql Fix theme={null}
      CREATE FUNCTION my_function RETURN PLS_INTEGER AS
      BEGIN
      RETURN 42;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables should not be shadowed">
    <div class="paragraph">
      <p>Overriding a variable declared in an outer scope can strongly impact the readability, and therefore the maintainability, of a piece of code. Further, it could lead maintainers to introduce bugs because they think they’re using one variable but are really using another.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo VARCHAR2(42) := 'foo';
      BEGIN
      DECLARE
      foo VARCHAR2(42) := 'bar'; -- Noncompliant - this variable hides the one above and should be renamed
      BEGIN
      DBMS_OUTPUT.PUT_LINE(foo); -- Displays "bar", which is confusing
      END;

      DBMS_OUTPUT.PUT_LINE(foo); -- Displays "foo"
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo VARCHAR2(42) := 'foo';
      BEGIN
      DECLARE
      bar VARCHAR2(42) := 'bar'; -- Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE(bar); -- Displays "bar"
      END;

      DBMS_OUTPUT.PUT_LINE(foo); -- Displays "foo"
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="A primary key should be specified during table creation">
    <div class="paragraph">
      <p>Tables without primary keys are largely unusable in a relational database because they cannot be joined to. A primary key should be specified at table creation to guarantee that all its records have primary key values.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      CREATE TABLE employee
      (
      employee_id INTEGER NOT NULL,
      first_name VARCHAR2(42) NOT NULL,
      last_name VARCHAR2(42) NOT NULL  
      );
      ```

      ```plsql Fix theme={null}
      CREATE TABLE employee
      (
      employee_id INTEGER PRIMARY KEY,
      first_name VARCHAR2(42) NOT NULL,
      last_name VARCHAR2(42) NOT NULL  
      );
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Return of boolean expressions should not be wrapped into an if-then-else statement">
    <div class="paragraph">
      <p>Return of boolean literal statements wrapped into <code>if-then-else</code> ones should be simplified.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      IF expression THEN
      RETURN TRUE;
      ELSE
      RETURN FALSE;
      END IF;
      ```

      ```plsql Fix theme={null}
      RETURN expression;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Column names should be used in a SQL ORDER BY clause">
    <div class="paragraph">
      <p>Even though the <code>ORDER BY</code> clause supports using column numbers, doing so makes the code difficult to read and maintain. Therefore the use of column names is preferred.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      BEGIN
      SELECT col2, col3
      BULK COLLECT INTO result
      FROM my_table
      ORDER BY
      1 ASC;           -- Noncompliant - if col1 is added to the selected fields, this may break
      END;
      /
      ```

      ```plsql Fix theme={null}
      BEGIN
      SELECT col2, col3
      BULK COLLECT INTO result
      FROM my_table
      ORDER BY
      col2 ASC;  
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Unused local variables should be removed">
    <div class="paragraph">
      <p>An unused local variable is a variable that has been declared but is not used anywhere in the block of code where it is defined. It is dead code, contributing to unnecessary complexity and leading to confusion when reading the code. Therefore, it should be removed from your code to maintain clarity and efficiency.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      seconds INTEGER := 0; -- Noncompliant - seconds is unused
      hours INTEGER := 2;
      BEGIN
      minutes := hours * 60;
      DBMS_OUTPUT.PUT_LINE('Number of minutes: ' || minutes);
      END;
      ```

      ```plsql Fix theme={null}
      DECLARE
      hours INTEGER := 2;
      BEGIN
      minutes := hours * 60;
      DBMS_OUTPUT.PUT_LINE('Number of minutes: ' || minutes);
      END;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Unused assignments should be removed">
    <div class="paragraph">
      <p>Dead stores refer to assignments made to local variables that are subsequently never used or immediately overwritten. Such assignments are unnecessary and don’t contribute to the functionality or clarity of the code. They may even negatively impact performance. Removing them enhances code cleanliness and readability.
      Even if the unnecessary operations do not do any harm in terms of the program’s correctness, they are - at best - a waste of computing resources.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      declare
      my_user VARCHAR2(30);
      my_date VARCHAR2(30);
      begin
      my_user := user();
      my_date := sysdate(); -- Noncompliant, the value of my_date is never read
      dbms_output.put_line('User:' || my_user || ', date: ' || my_user); 
      end;
      ```

      ```plsql Fix theme={null}
      declare
      my_user VARCHAR2(30);
      my_date VARCHAR2(30);
      begin
      my_user := user();
      my_date := sysdate();
      dbms_output.put_line('User:' || my_user || ', date: ' || my_date);
      end;
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables should comply with a naming convention">
    <div class="paragraph">
      <p>A naming convention in software development is a set of guidelines for naming code elements like variables, functions, and classes.
      \{identifier\_capital\_plural} hold the meaning of the written code. Their names should be meaningful and follow a consistent and easily recognizable pattern.
      Adhering to a consistent naming convention helps to make the code more readable and understandable, which makes it easier to maintain and debug.
      It also ensures consistency in the code, especially when multiple developers are working on the same project.</p>
    </div>

    <div class="paragraph">
      <p>This rule checks that \{identifier} names match a provided regular expression.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      DECLARE
      badVariable_ PLS_INTEGER; -- Non-Compliant
      BEGIN
      NULL;
      END;
      /
      ```

      ```plsql Fix theme={null}
      DECLARE
      goodVariable PLS_INTEGER; -- Compliant
      BEGIN
      NULL;
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Variables should not be shadowed">
    <div class="paragraph">
      <p>Variables should not be shadowed</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo VARCHAR2(42) := 'foo';
      BEGIN
      DECLARE
      foo VARCHAR2(42) := 'bar'; -- Noncompliant - this variable hides the one above and should be renamed
      BEGIN
      DBMS_OUTPUT.PUT_LINE(foo); -- Displays "bar", which is confusing
      END;

      DBMS_OUTPUT.PUT_LINE(foo); -- Displays "foo"
      END;
      /
      ```

      ```plsql Fix theme={null}
      SET SERVEROUTPUT ON

      DECLARE
      foo VARCHAR2(42) := 'foo';
      BEGIN
      DECLARE
      bar VARCHAR2(42) := 'bar'; -- Compliant
      BEGIN
      DBMS_OUTPUT.PUT_LINE(bar); -- Displays "bar"
      END;

      DBMS_OUTPUT.PUT_LINE(foo); -- Displays "foo"
      END;
      /
      ```
    </CodeGroup>
  </Accordion>

  <Accordion title="Two branches in a conditional structure should not have exactly the same implementation">
    <div class="paragraph">
      <p>Having two branches in an IF/ELSIF chain with the same implementation is at best duplicate code, and at worst a coding error.</p>
    </div>

    <CodeGroup>
      ```plsql Bad theme={null}
      IF param = 1 THEN
      sort_order := 0;
      column := 'LastName';
      ELSIF param = 2 THEN
      sort_order := 0;
      column := 'LastName'; -- Noncompliant
      ELSE
      sort_order := 1;
      column := 'FirstName';
      END IF;
      ```

      ```plsql Fix theme={null}
      IF param = 1 OR param = 2 THEN
      sort_order := 0;
      column := 'LastName';
      ELSE
      sort_order := 1;
      column := 'FirstName';
      END IF;
      ```
    </CodeGroup>
  </Accordion>
</AccordionGroup>