Home

NUMBER variables should be declared with precision

Description

    The rule "NUMBER variables should be declared with precision" states that when declaring a NUMBER variable in PL/SQL, the precision should be specified. This means that the total number of digits and the number of digits to the right of the decimal point should be specified. This helps to ensure that the variable can store the desired value without any loss of precision.

Key Benefits

  • Precision: NUMBER variables should be declared with precision rule to ensure accuracy and consistency of calculations.
  • Efficiency: Declaring precision rule for NUMBER variables can help reduce the amount of memory and processing time required.
  • Reliability: By declaring precision rule for NUMBER variables, it can help to avoid unexpected results due to rounding errors.

 

Non-compliant Code Example

CREATE OR REPLACE PROCEDURE GetCustomerPhoneNumber (customerId IN INTEGER)
IS
 oracleQuery  VARCHAR2(100);
 customerPhoneNumber NUMBER;             --Non compliant code (NUMBER variable declared without precision)
 
BEGIN
 oracleQuery :=    q'{SELECT PhoneNumber FROM Customers }'
         ||    q'{WHERE id = '}'
         ||    customerId
         ||    q'{'}';
 EXECUTE IMMEDIATE oracleQuery INTO customerPhoneNumber;
END;

Compliant Code Example

CREATE OR REPLACE PROCEDURE GetCustomerPhoneNumber (customerId IN INTEGER)
IS
 oracleQuery  VARCHAR2(100);
 customerPhoneNumber NUMBER(10);         --Compliant code (NUMBER variable declared with precision)
 
BEGIN
 oracleQuery :=    q'{SELECT PhoneNumber FROM Customers }'
         ||    q'{WHERE id = '}'
         ||    customerId
         ||    q'{'}';
 EXECUTE IMMEDIATE oracleQuery INTO customerPhoneNumber;
END;
Visual Expert 2024
 VEPLSQLRULE155