Home

Description

The Pl_Sql code rule "Variables should not be initialized with NULL" states that variables should not be assigned the value NULL when they are declared. This is because NULL is a special value that indicates that the variable has no value assigned to it. Instead, variables should be assigned an appropriate value that reflects the data type of the variable. This will ensure that the variable is properly initialized and can be used in the code.

Key Benefits

• Ensures Consistency: Ensures that all variables are initialized with the same value, avoiding potential errors.
• Improves Performance: Initializing variables with NULL can slow down the program as it takes more time to assign a value.
• Improves Readability: By initializing variables with not NULL, it is easier to identify which variables have been initialized and which have not.

Non-compliant Code Example

CREATE FUNCTION GetCustomers(cust CUSTOMER_T) RETURN CUSTOMERS_T
PIPELINED IS
  out_cust CUSTOMERS_T := CUSTOMERS_T(NULL);        --Non compliant code (Variable initialized with NULL)
  in_cust cust%ROWTYPE;
BEGIN
  LOOP
    FETCH cust INTO in_cust; 
    EXIT WHEN cust%NOTFOUND;
    
    out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    PIPE ROW(out_cust);
    
    out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    PIPE ROW(out_cust);
  END LOOP;
  CLOSE cust;
  RETURN;
END

Compliant Code Example

CREATE FUNCTION GetCustomers(cust CUSTOMER_T) RETURN CUSTOMERS_T
PIPELINED IS
  out_cust CUSTOMERS_T := CUSTOMERS_T();  --Compliant code
  in_cust cust%ROWTYPE;
BEGIN
  LOOP
    FETCH cust INTO in_cust; 
    EXIT WHEN cust%NOTFOUND;
    
    out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    PIPE ROW(out_cust);
    
    out_cust.first_name := in_cust.first_name;
    out_cust.last_name := in_cust.last_name;
    out_cust.credit_limit := in_cust.credit_limit;
    PIPE ROW(out_cust);
  END LOOP;
  CLOSE cust;
  RETURN;
END