1. Use Soft Parsing ( Bind variables) instead of hard parsing
(https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082)
2. Use Table Cast if reuired
3. Use EXISTS instead of IN Operator
4. Maintain Comments across Code
5. Local Variable names prefix with V_
Global Variables prefix with G_
Constant variables prefix with C_
Date variables prefix with D_
Function Name Prefix with F_
Procedure name prefix with P_
Type names prefix with T_
Object names prefix with O_
Cursor Names prefix with Cur_
Ref cursor Variables prefix with Ref_
6. Try to avoid Loops as much as possible
7. Try to Avoid Like Operator use = if possible
8. Use appropriate Hints if required
9. Use EXECUTE IMMEDIATE Statement to perform DDL, DML Operations (Dynamic SQL)
10. Use NOLOGGING while creating table dynamically
11. Reuse code from common packages if required and create common packages if useful to all API's
12. Use Global Temporary tables if request xml contains any lists ( occurance >1)
13. Follow Indentation better to understand the code
************************************************ CRITICAL ************************************************************
1. SQL - Avoid CROSS JOIN queries
--------------------------------------------------------------------------------------------------------------------------------
A CROSS JOIN 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").
Such a query can return a huge amount of data, and therefore should be used only with great caution and only when really needed.
Here are the 2 kind of syntaxes for cross join queries:
BEGIN
-- Standard ANSI syntax
SELECT *
INTO employeeArray
FROM employee CROSS JOIN department; -- Non-Compliant
END;
/
and
BEGIN
-- Old syntax
SELECT *
INTO employeeArray
FROM employee, department; -- Non-Compliant
END;
/
2. SQL - Avoid using DELETE or UPDATE without a WHERE clause
-------------------------------------------------------------------------------------------------------------------------
DELETE or UPDATE queries have most of the time a WHERE clause. Therefore, the rare cases in which the WHERE clause is missing should be reviewed to ensure that it was not unintentionally forgotten - which could lead to data loss.
For example:
DECLARE
maxAge PLS_INTEGER := 60;
BEGIN
UPDATE employee SET status = 'retired'; -- Non-Compliant - the WHERE was forgotten
END;
/
should be replaced by:
DECLARE
maxAge PLS_INTEGER := 60;
BEGIN
UPDATE employee SET status = 'retired' WHERE age > maxAge; -- Compliant
END;
/
3. Pipelined functions should have at least one PIPE ROW statement and not return any expression (PLS-00633)
-----------------------------------------------------------------------------------------------------------------------
Pipelined functions offers the ability to create programmatically generated tables. One of the benefits of such functions is that they reduce memory consumption as results are not all kept in memory before being returned. Instead of relying on RETURN, PIPE ROW must be used to return the results, one row at a time.
Trying to return an expression from a pipelined function raises PLS-00633: RETURN statement in a pipelined function cannot contain an expression
For instance:
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 -- Non-Compliant, 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; -- Non-Compliant, will raise PLS-00633
END;
/
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3 PLS-00633: RETURN statement in a pipelined function cannot
contain an expression
SELECT * FROM TABLE(foo());
DROP FUNCTION foo;
DROP TYPE myTableType;
DROP TYPE myScalarType;
should be refactored into:
-- ...
CREATE OR REPLACE FUNCTION foo RETURN myTableType PIPELINED AS -- Compliant
BEGIN
FOR i IN 1 .. 3 LOOP
PIPE ROW(myScalarType('Dummy ' || i));
END LOOP;
RETURN; -- Compliant
END;
/
SQL> SELECT * FROM TABLE(foo());
DUMMY
------------------------------------------
Dummy 1
Dummy 2
Dummy 3
4. PL/SQL - Do not mix positional and named arguments in invocations (PLS-00312)
----------------------------------------------------------------------------------------------------
For better readability, and to prevent the PLS-00312: a positional parameter association may not follow a named association exception from being raised,
do not mix named and positional argument invocations.
The following code snippet illustrates this rule:
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
sub(10, op2 => 2); -- Non-Compliant
sub(op1 => 10, 2); -- Non-Compliant - raises PLS-00312: a positional parameter association may not follow a named association
END;
/
5. Sensitive SYS owned functions should not be used
--------------------------------------------------------------------------------------------------------------
Some Oracle packages contain powerful SYS owned functions that can be used to perform malicious operations.
For instance, DBMS_SYS_SQL.PARSE_AS_USER can be used to execute a statement as another user.
Most programs do not need those functions and this rule allows to hunt them in order to prevent security risks.
Example:
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(); -- Non-Compliant
-- 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); -- Non-Compliant
END;
/
List of package names that should be detected Default Value:
DBMS_SYS_SQL,DBMS_BACKUP_RESTORE,DBMS_LOB,DBMS_NAMESPACE,DBMS_SCHEDULER,DBMS_RANDOM,EMD_SYSTEM,UTL_FILE,UTL_HTTP,UTL_SMTP,UTL_TCP
*************************************** BLOCKER **************************************************
1. PL/SQL - Do not declare a variable more than once in a given scope (PLS-00371)
-------------------------------------------------------------------------------------------
At most one declaration of a variable in a given scope is allowed in PL/SQL. The PLS-00371 error will be raised at runtime when attempting to reference a variable declared more than once.
The following code snippet:
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;
/
should be refactored into:
SET SERVEROUTPUT ON
DECLARE
foo VARCHAR2(42) := 'foo';
bar VARCHAR2(42) := 'bar'; -- Compliant
BEGIN
DBMS_OUTPUT.PUT_LINE(foo);
END;
/
2. SQL - Do not COMMIT/ROLLBACK from a trigger unless it is in an autonomous transaction
--------------------------------------------------------------------------------------------------
Calling COMMIT or ROLLBACK from within a trigger will lead to an ORA-04092 exception, unless the trigger has its own autonomous transaction.
The following code snippet illustrates this rule:
SET SERVEROUTPUT ON
CREATE TABLE accounts(
balance NUMBER
);
INSERT INTO accounts VALUES(0);
CREATE TABLE log(
message VARCHAR2(100)
);
-- Version 1: Does not log anything, as the INSERT is rolled back together with the main transaction
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);
END;
/
-- Version 2: Fails with a "ORA-04092: cannot COMMIT in a trigger", the trigger cannot commit the main transaction
CREATE OR REPLACE 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; -- Non-Compliant, will fail with a ORA-04092
END;
/
-- Version 3: This is the correct way
CREATE OR REPLACE 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 = 99;
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;
3. NULL - Use IS NULL and IS NOT NULL instead of direct NULL comparisons
----------------------------------------------------------------------------------------
In PL/SQL, the following code snippet:
SET SERVEROUTPUT ON
DECLARE
name VARCHAR2(42) := ''; /* This is equivalent to name := NULL; */
BEGIN
IF name = '' THEN /* Non-Compliant - always evaluates to FALSE and is equivalent to name = NULL */
DBMS_OUTPUT.PUT_LINE('True'); /* Unreachable */
ELSE
DBMS_OUTPUT.PUT_LINE('False');
END IF;
END;
/
will display False, which is counter-intuitive and can lead to unreachable code.
The correct way to write the code above is:
SET SERVEROUTPUT ON
DECLARE
name VARCHAR2(42) := ''; /* This is equivalent to name := NULL; */
BEGIN
IF name IS NULL THEN /* Compliant */
DBMS_OUTPUT.PUT_LINE('True');
ELSE
DBMS_OUTPUT.PUT_LINE('False');
END IF;
END;
/
4. PL/SQL - A variable declared NOT NULL must have an initialization assignment (PLS-00218)
-------------------------------------------------------------------------------------------------------
Variables and fields declared as NOT NULL must be immediately initialized, as they cannot be implicitly initialized to the NULL value. This rule prevents PLS-00218 exceptions from being raised at runtime.
The following code snippet illustrates this rule:
SET SERVEROUTPUT ON
DECLARE
test PLS_INTEGER; -- This variable is implicitly initialized to NULL
foo1 PLS_INTEGER NOT NULL; -- Non-Compliant PLS-00218 a variable declared NOT NULL must have an initialization assignment
foo2 PLS_INTEGER NOT NULL := 42; -- Compliant
TYPE myType IS RECORD(
foo1 PLS_INTEGER NOT NULL, -- Non-Compliant PLS-00218 a variable declared NOT NULL must have an initialization assignment
foo2 PLS_INTEGER NOT NULL := 42, -- Compliant
bar PLS_INTEGER NULL
);
BEGIN
IF test IS NULL THEN
DBMS_OUTPUT.PUT_LINE('test is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('test is NOT NULL');
END IF;
END;
/
5. DATATYPES - Do not constrain types that cannot be constrained (PLS-00566)
--------------------------------------------------------------------------
Some types cannot be constrained, and attempting to do so results in the exception PLS-00566: type name "..." cannot be constrained being raised.
The following code:
DECLARE
foo BLOB(42); -- Non-Compliant - raises PLS-00566: type name "BLOB" cannot be constrained
BEGIN
NULL;
END;
/
should be refactored into:
DECLARE
foo BLOB; -- Compliant
BEGIN
NULL;
END;
/
6. DATATYPES - Do not specify a scale on float types (PLS-00510)
--------------------------------------------------------------------------
Float data types, such as FLOAT, DOUBLE PRECISION or REAL cannot have a scale constraint. Trying to specify a scale results in the exception PLS-00510: Float cannot have scale being raised.
The following code:
DECLARE
foo FLOAT(10, 3); -- Non-Compliant - raises PLS-00510
BEGIN
NULL;
END;
/
should be refactored into:
DECLARE
foo FLOAT(10); -- Compliant
BEGIN
NULL;
END;
/
7. DATATYPES - Do not use improper constraint forms (PLS-00572)
------------------------------------------------------------------------------
Not every data types supports the RANGE or scale constraints. Using these constraints on those types result in an PLS-00572: improper constraint form used exception being raised.
The following code:
DECLARE
foo INTEGER RANGE 0 .. 42; -- Non-Compliant - raises PLS-00572 as NUMBER does not support the RANGE constraint
BEGIN
NULL;
END;
/
should be refactored into:
DECLARE
foo INTEGER; -- Compliant
BEGIN
NULL;
END;
/
8. PL/SQL - RAISE_APPLICATION_ERROR must raise only error with code between -20'000 and -20'999 (ORA-21000)
----------------------------------------------------------------------------------------------------------------
RAISE_APPLICATION_ERROR must be called with an error code between -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
The following code:
BEGIN
RAISE_APPLICATION_ERROR(0, 'This is an application error'); -- Non-Compliant - raises ORA-21000
END;
/
should be refactored into:
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'This is an application error'); -- Compliant
END;
/
9. DATATYPES - Do not constrain anchored (%TYPE or %ROWTYPE) types
-----------------------------------------------------------------------------------
Anchored types, i.e. specified using either %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.
The following code:
DECLARE
foo DUAL.DUMMY%TYPE(42); -- Non-Compliant - raises PLS-00573
BEGIN
NULL;
END;
/
should be refactored into:
DECLARE
foo DUAL.DUMMY%TYPE; -- Compliant
BEGIN
NULL;
END;
/
10. DATATYPES - Do not use the BYTE length semantic on NCHAR / NVARCHAR2 (PLS-00639)
-----------------------------------------------------------------------------------------------------------------
NCHAR and NVARCHAR2 length semantic must be given in characters and not in bytes. Indeed, a single national character may occupy more than a single byte in memory. Oracle will allocate the appropriate amount 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 exception being raised.
The following code:
DECLARE
foo NCHAR(42 BYTE); -- Non-Compliant - raises PLS-00639
BEGIN
NULL;
END;
/
should be refactored into:
DECLARE
foo NCHAR(42); -- Compliant
bar NCHAR(42 CHAR); -- Also compliant, as an alternative
BEGIN
NULL;
END;
/
11. DATATYPES - Specify the size constraint on string types (PLS-00215)
---------------------------------------------------------------------------------
String data types, such as 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) being raised.
The following code:
DECLARE
foo VARCHAR2; -- Non-Compliant - raises PLS-00215
BEGIN
NULL;
END;
/
should be refactored into:
DECLARE
foo VARCHAR2(42); -- Compliant
BEGIN
NULL;
END;
/
12. PACKAGE - Avoid RETURN statements in the initialization section of PACKAGE BODY
-----------------------------------------------------------------------------------------------------
In a CREATE PACKAGE BODY, the purpose of the initialization section part is to set the initial values of the packages global variables. It is therefore surprising to find a RETURN statement there, as all its following statements will be unreachable.
The following code snippet illustrates this rule:
SET SERVEROUTPUT ON
CREATE OR REPLACE PACKAGE foo1 AS
FUNCTION getBar RETURN PLS_INTEGER;
bar PLS_INTEGER;
END;
/
CREATE OR REPLACE PACKAGE BODY foo1 AS
FUNCTION getBar RETURN PLS_INTEGER AS
BEGIN
RETURN bar; -- Compliant
END;
BEGIN
bar := 42;
DBMS_OUTPUT.PUT_LINE('package loaded');
RETURN; -- Non-Compliant
DBMS_OUTPUT.PUT_LINE('this is unreachable code');
END;
/
DROP PACKAGE BODY foo;
DROP PACKAGE foo;
PL/SQL - Do not declare a variable more than once in a given scope (PLS-00371)
plsql:VariableRedeclaration
Bug Blocker No tags Available Since June 19, 2013 SonarQube (PL/SQL) Constant/issue: 1h
At most one declaration of a variable in a given scope is allowed in PL/SQL. The PLS-00371 error will be raised at runtime when attempting to reference a variable declared more than once.
The following code snippet:
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;
/
should be refactored into:
SET SERVEROUTPUT ON
DECLARE
foo VARCHAR2(42) := 'foo';
bar VARCHAR2(42) := 'bar'; -- Compliant
BEGIN
DBMS_OUTPUT.PUT_LINE(foo);
END;
/
http://dist.sonarsource.com/reports/coverage/rules_in_plsql.html
***************************************** 1 to 20 MINOR ***************************************************************************************************
1. Single line comments should prefixed by "--"
-----------------------------------------------------------------------------------------------------
The multi-line comment syntax "/* ... */" should not be used for single line comments, for which the "--" syntax is more appropriate.
The following code snippet illustrates this rule:
-- This comment line is Compliant/Standard
/*
These comment lines are also Compliant
comment 1
comment 2
*/
/*
This comment line is also Compliant
*/
/* But this one is bad and should be rewritten using the "--" syntax Non-Compliant */
FORMAT - Each line in a /* ... */ comment should start with a "*"
Multi-line comments are more readable when each line is aligned using the "*" character. At most one violation is created for each comment
The following code snippet illustrates this rule:
/*
this line is not aligned and ugly Non-Compliant
no violation is created on this line, even though is it also bad
*/
/*
* this is much better Compliant
*/
/* this is Compliant */
2.FORMAT - Reserved words should be written in upper case
--------------------------------------------------------------------------------------------------
Writing reserved words in upper case increases the code readability.
The following code snippet illustrates this rule:
BEGIN
NULL;
null; -- Non-Compliant
END;
/
Reserved words should be written in lower case
All reserved words should be written using the same case to ensure consistency in the code. This rule checks that they are all in lower case.
The following code snippet illustrates this rule:
begin
null;
NULL; -- Non-Compliant
end;
/
3. Complex IF statements should be replaced by CASE ones
---------------------------------------------------------------------------------------------------
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.
For instance, the following code:
DECLARE
x PLS_INTEGER := 0;
BEGIN
IF x = 0 THEN -- Non-Compliant
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;
/
should be refactored into:
DECLARE
x PLS_INTEGER := 0;
BEGIN
CASE x -- Compliant
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;
/
and, the following code:
DECLARE
x PLS_INTEGER := 0;
y PLS_INTEGER := 0;
BEGIN
IF x = 0 THEN -- Non-Compliant
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;
/
should be refactored into:
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;
/
4.FUNCTIONS - The last statement must be a RETURN one
-----------------------------------------------------------------------------------------------------------
Always having a RETURN statement as the last one in a function is a good practice for two main reasons:
It prevents the ORA-06503 "PL/SQL: Function returned without value" error.
It prevents unreachable code from being added after the RETURN.
The following code snippet illustrates this rule:
CREATE FUNCTION incorrectFunction1 RETURN PLS_INTEGER IS -- Non-Compliant
BEGIN
RETURN 42;
-- This is unreachable code
NULL;
END;
/
DROP FUNCTION incorrectFunction1;
CREATE FUNCTION incorrectFunction2 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 incorrectFunction2;
CREATE FUNCTION correctFunction RETURN PLS_INTEGER IS -- Compliant
BEGIN
RETURN 42;
END;
/
DROP FUNCTION correctFunction;
5.FORMAT - Avoid initializing variables with null
---------------------------------------------------------------------------------------
Explicit variable initialization with null values are superfluous, since they are already implicitly initialized to null.
The following code snippet illustrates this rule:
SET SERVEROUTPUT ON
DECLARE
foo PLS_INTEGER := NULL; -- Non-Compliant, the null assignation is superfluous/unnecessary
bar VARCHAR2(100) := ''; -- Non-Compliant, the null assignation is superfluous
correctEmptyString 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;
IF correctEmptyString IS NULL THEN
DBMS_OUTPUT.PUT_LINE('correctEmptyString is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('correctEmptyString is NOT NULL');
END IF;
END;
/
Output:
foo is NULL
correctEmptyString is NULL
PL/SQL procedure successfully completed.
6. FORMAT - Avoid long lines
----------------------------------------------------------------------------------------------
Keep the line length small for better readability of the code.
The maximum authorized line length --> Default Value:80
7. LITERALS - Avoid using magic literals
---------------------------------------------------------------------------------------------
8. NAMING CONVENTION - Constants
---------------------------------------------------------------------------------------------
Each constant name must match a given regular expression.
The following code snippet illustrates this rule:
DECLARE
goodConstant CONSTANT PLS_INTEGER := 42; -- Compliant
badConstant_ CONSTANT PLS_INTEGER := 42; -- Non-Compliant
BEGIN
NULL;
END;
/
The regular expression the name should match --> Default Value:[a-zA-Z]([a-zA-Z0-9_]*[a-zA-Z0-9])?
9. NAMING CONVENTION - Cursors
----------------------------------------------------------------------------------------------------
Each cursor name must match a given regular expression.
The following code snippet illustrates this rule:
CREATE TABLE employee(
name VARCHAR2(42)
);
DECLARE
CURSOR goodCursor RETURN employee%ROWTYPE; -- Compliant
CURSOR badCursor_ RETURN employee%ROWTYPE; -- Non-Compliant
CURSOR goodCursor RETURN employee%ROWTYPE IS SELECT * FROM employee; -- Compliant
CURSOR badCursor_ RETURN employee%ROWTYPE IS SELECT * FROM employee; -- Non-Compliant
BEGIN
NULL;
END;
/
DROP TABLE employee;
10. NAMING CONVENTION - Exceptions
-------------------------------------------------------------------------------------------------------
Each exception name must match a given regular expression.
The following code snippet illustrates this rule:
DECLARE
goodException EXCEPTION; -- Compliant
badException_ EXCEPTION; -- Non-Compliant
BEGIN
NULL;
END;
/
The regular expression the name should match --> Default Value:[a-zA-Z]([a-zA-Z0-9_]*[a-zA-Z0-9])?
11. NAMING CONVENTION - Functions and procedures parameters
----------------------------------------------------------------------------------------------------------
Each function and procedure parameter name must match a given regular expression.
The following code snippet illustrates this rule:
DECLARE
FUNCTION myfunction1(goodParameter PLS_INTEGER) RETURN PLS_INTEGER; -- Compliant
FUNCTION myfunction2(badParameter_ PLS_INTEGER) RETURN PLS_INTEGER; -- Non-Compliant
PROCEDURE myprocedure1(goodParameter PLS_INTEGER); -- Compliant
PROCEDURE myprocedure2(badParameter_ PLS_INTEGER); -- Non-Compliant
FUNCTION myfunction1(goodParameter PLS_INTEGER) RETURN PLS_INTEGER AS -- Compliant
BEGIN
RETURN 42;
END;
FUNCTION myfunction2(badParameter_ PLS_INTEGER) RETURN PLS_INTEGER AS -- Non-Compliant
BEGIN
RETURN 42;
END;
PROCEDURE myprocedure1(goodParameter PLS_INTEGER) AS -- Compliant
BEGIN
NULL;
END;
PROCEDURE myprocedure2(badParameter_ PLS_INTEGER) AS -- Non-Compliant
BEGIN
NULL;
END;
BEGIN
NULL;
END;
/
The regular expression the name should match --> Default Value:[a-zA-Z]([a-zA-Z0-9_]*[a-zA-Z0-9])?
12. NAMING CONVENTION - Object attributes
----------------------------------------------------------------------------------------
Sharing some naming conventions is a key point to make it possible for a team to efficiently collaborate. This rule allows to check that all object attribute names match a provided regular expression.
The following code:
CREATE TYPE my_type AS OBJECT(
foo__bar INTEGER -- Non-Compliant
);
/
DROP TYPE my_type;
should be refactored into:
CREATE TYPE my_type AS OBJECT(
foo_bar INTEGER -- Compliant
);
/
DROP TYPE my_type;
The regular expression the name should match Default Value:[a-zA-Z](_?+[a-zA-Z0-9])*+
13. NAMING CONVENTION - Record fields
----------------------------------------------------------------------
Sharing some naming conventions is a key point to make it possible for a team to efficiently collaborate. This rule allows to check that all record field names match a provided regular expression.
The following code:
DECLARE
TYPE my_type IS RECORD(
foo__bar PLS_INTEGER -- Non-Compliant
);
BEGIN
NULL;
END;
/
should be refactored into:
DECLARE
TYPE my_type IS RECORD(
foo_bar PLS_INTEGER -- Compliant
);
BEGIN
NULL;
END;
/
14. NAMING CONVENTION - Variables
-----------------------------------------------------------------------------------------------
Each variable name must match a given regular expression.
The following code snippet illustrates this rule:
DECLARE
goodVariable PLS_INTEGER; -- Compliant
badVariable_ PLS_INTEGER; -- Non-Compliant
BEGIN
NULL;
END;
/
The regular expression the name should match Default Value:[a-zA-Z]([a-zA-Z0-9_]*[a-zA-Z0-9])?
15. PARAMETER - Ensure parameters mode is explicitly specified as IN
----------------------------------------------------------------------------------
By default, the parameter mode is IN. However, mentioning it explicitly does make the code easier to read.
The following code snippet illustrates this rule:
SET SERVEROUTPUT ON
DECLARE
PROCEDURE printNameBad(name VARCHAR2) AS -- Non-Compliant, avoid to rely on the default mode
BEGIN
DBMS_OUTPUT.PUT_LINE('name: ' || name);
END;
PROCEDURE printNameGood(name IN VARCHAR2) AS -- Compliant
BEGIN
DBMS_OUTPUT.PUT_LINE('name: ' || name);
END;
BEGIN
printNameBad('Foo');
printNameGood('Bar');
END;
/
16. NAMING CONVENTION - Function and procedure names
-----------------------------------------------------------------------------------------------
Each function and procedure name must match a given regular expression.
The following code snippet illustrates this rule:
CREATE FUNCTION goodFunction RETURN PLS_INTEGER AS -- Compliant
BEGIN
RETURN 42;
END;
/
CREATE FUNCTION badFunction_ RETURN PLS_INTEGER AS -- Non-Compliant
BEGIN
RETURN 42;
END;
/
DROP FUNCTION goodFunction;
DROP FUNCTION badFunction_;
The regular expression function and procedure names should match Default Value:[a-zA-Z]([a-zA-Z0-9_]*[a-zA-Z0-9])?
17. NAMING CONVENTION - Package names
-------------------------------------------------------------------------------------------------------------------
Each package name must match a given regular expression.
The regular expression package names should match Default Value: [a-zA-Z]([a-zA-Z_]*[a-zA-Z])?
18. Return of boolean expressions should not be wrapped into an "IF-THEN-ELSE" statement
------------------------------------------------------------------------------------------
Return of boolean literal statements wrapped into IF-THEN-ELSE ones should be simplified.
Noncompliant Code Example
IF expression THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
Compliant Solution
RETURN expression;
19. String literals should not be duplicated
-------------------------------------------------------------------------------------
Duplicated string literals make the process of refactoring error-prone, since you must be sure to update all occurrences. On the other hand, constants can be referenced from many places, but only need to be updated in a single place.
Noncompliant Code Example
BEGIN
prepare('action1');
execute('action1');
release('action1');
END;
/
Compliant Solution
DECLARE
action CONSTANT VARCHAR2(7) := 'action1';
BEGIN
prepare(action);
execute(action);
release(action);
END;
/
20. Comments should not be located at the end of lines of code
--------------------------------------------------------------------------------------------------
This rule verifies that single-line comments are not located at the end of a line of code. The main idea behind this rule is that in order to be really readable, trailing comments would have to be property 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.
However, this rule allows to write trailing "metadata comments" - for which the pattern is configurable, as those metadata comments are usually very short and heavily used in some cases.
// The following line is non-compliant
int a1 = b + c; // This is a trailing comment that can be very very long
// This very long comment is better placed before the line of code
int a2 = b + c;
// The following line is compliant with the default configuration of the rule
String a3 = "id"; // $NON-NLS-1$
Pattern for text of trailing comments that are allowed.Default Value:(?s)^(?:\s*+[^\s]++|\+.*+)$
******************************************* 21,22,23,24 - INFO ************************************************************************************
21. SQL - Review each usage of FULL OUTER JOIN
-----------------------------------------------------------------------
Many developers do not properly understand full outer joins, as it is not commonly used. Therefore, each usage of this language feature should be reviewed.
The following code illustrates this rule:
BEGIN
SELECT *
BULK COLLECT INTO result
FROM DUAL d1
FULL OUTER JOIN DUAL d2 ON d1.dummy != d2.dummy; -- Non-Compliant
END;
/
22. Avoid the usage of the NOSONAR marker
-----------------------------------------------------------------------------------------------
Any violation can be deactivated with the using the NOSONAR marker.
This marker is useful to exclude specific exceptions but can abusively used to hide real quality flaws.
This rule allows to review and/or forbid the use of this marker.
The following code snippet illustrates this rule:
DECLARE
FUNCTION GET_ANSWER RETURN PLS_INTEGER IS
BEGIN
RETURN 42; -- NOSONAR This magic number is allowed here as an exception Non-Compliant
END;
BEGIN
NULL;
END;
/
23. PL/SQL parser failure
--------------------------------------------------------------------------------------------
When the PL/SQL parser fails, it is possible to record the failure as a violation on the file. This way, not only it is possible to track the number of files that do not parse but also to easily find out why they do not parse.
24. SQL - Avoid using UNION
----------------------------------------------------------------------------
UNION can sometimes cause performance issues and should therefore be used with care.
The following code illustrates this rule:
SELECT DUMMY FROM DUAL
UNION -- Non-Compliant
SELECT DUMMY FROM DUAL@dblink;
