Home

Description

The rule "PLS_INTEGER types should be used" states that the PLS_INTEGER data type should be used when declaring variables, constants, and parameters in a PL/SQL program. PLS_INTEGER is a data type that is specifically designed for use in PL/SQL programs. It is a 32-bit signed integer data type that can store values between -2,147,483,648 and 2,147,483,647. This data type is more efficient than other integer data types, such as NUMBER, and is more reliable when dealing with large numbers. Using PLS_INTEGER types can help to improve the performance of a PL/SQL program.

Key Benefits

• Range: PLS_INTEGER types provide a wider range than other numeric data types, allowing for a greater range of values.
• Speed: PLS_INTEGER types are faster to process than other numeric data types, making them ideal for applications that require quick response times.
• Accuracy: PLS_INTEGER types are more accurate than other numeric data types, providing more precise results.

Non-compliant Code Example

DECLARE
  oracleQuery VARCHAR2(600);
  new_Custid  NUMBER(8);            --Non compliant code (Column is not of type PLS_INTEGER)
  new_FirstName   VARCHAR2(50);
  new_LastName   VARCHAR2(50);
BEGIN
	oracleQuery := 'BEGIN CreateCustomer(:id, :Fname, :Lname); END;';
	EXECUTE IMMEDIATE oracleQuery USING IN OUT new_Custid, new_FirstName, new_LastName;
END;

Compliant Code Example

DECLARE
  oracleQuery VARCHAR2(600);
  new_Custid  PLS_INTEGER;            --Compliant code (Column is of type PLS_INTEGER)
  new_FirstName   VARCHAR2(50);
  new_LastName   VARCHAR2(50);
BEGIN
	oracleQuery := 'BEGIN CreateCustomer(:id, :Fname, :Lname); END;';
	EXECUTE IMMEDIATE oracleQuery USING IN OUT new_Custid, new_FirstName, new_LastName;
END;