Home

Variables should be nullable

Description

    The Pl_Sql code rule "Variables should be nullable" states that variables should be declared with the NULL keyword, allowing them to accept a NULL value. This is important because it allows the variable to accept a NULL value without generating an error. This is especially useful when dealing with database operations, as it allows for the possibility of missing or unknown values. This rule also helps to ensure that the code is more robust and can handle unexpected data.

Key Benefits

  • Eliminates potential bugs: By allowing variables to be set to null, the potential for bugs caused by uninitialized variables is eliminated.
  • Improves code readability: By explicitly setting variables to null, code readability is improved as it is easier to identify which variables have been initialized.
  • Reduces memory usage: By allowing variables to be set to null, memory usage is reduced as variables are not initialized until they are needed.

 

Non-compliant Code Example

DECLARE 
  firstname VARCHAR(75) NOT NULL;           --Non compliant code (Variable is not null-able)
  lastname VARCHAR(75);
  landmark NVARCHAR2(75) NOT NULL;          --Non compliant code (Variable is not null-able)

BEGIN
	SELECT FIRSTNAME, LASTNAME, LANDMARK 
	INTO firstname,lastname,landmark
	FROM CUSTOMERS
	WHERE (CREDIT_LIMIT = 1800 AND CREDIT_LIMIT > 1100);
END;

Compliant Code Example

DECLARE 
  firstname VARCHAR(75);           --Compliant code (Variable is null-able)
  lastname VARCHAR(75);
  landmark NVARCHAR2(75);          --Compliant code (Variable is null-able)

BEGIN
	SELECT FIRSTNAME, LASTNAME, LANDMARK 
	INTO firstname,lastname,landmark
	FROM CUSTOMERS
	WHERE (CREDIT_LIMIT = 1800 AND CREDIT_LIMIT > 1100);
END;
Visual Expert 2024
 VEPLSQLRULE70