Home

Description

This rule states that when using PL/SQL code, strings should only be stored in variables or columns that are large enough to contain them. This is important to ensure that the data is not truncated and that the code runs correctly. It is also important to consider the size of the string when allocating memory for the variable or column, as this can affect performance.

Key Benefits

• Efficient Storage: Strings should only be moved to variables or columns which are large enough to hold them, ensuring efficient storage of data.
• Data Integrity: By using variables or columns of the appropriate size, data integrity is maintained as the data is stored in its entirety.
• Data Security: By ensuring that strings are stored in the correct size variables or columns, data security is improved as the data is not vulnerable to being truncated or corrupted.

Non-compliant Code Example

CREATE TABLE CUSTOMERDETAILS (id number, first_name varchar2(30),landmark varchar2);

INSERT INTO CUSTOMERDETAILS (id, first_name, landmark) VALUES (1, 'Alice', 'Near H P Hospital'); 

CREATE OR REPLACE PROCEDURE GetCustomerName
IS
  customerName varchar2(15);
BEGIN
	SELECT first_name into customerName from CUSTOMERDETAILS where Id = 152; --Non compliant code (first_name column is of length 30 and tried to assign variable customerName of length 15)
END;

PROCEDURE UpdateCustomerName(id_v in number, first_name_v in nvarchar2(75), landmark_v in nvarchar2)
IS
BEGIN
    UPDATE CUSTOMERDETAILS
    SET first_name=first_name_v, landmark=landmark_v --Non compliant code (first_name_v is variable is of length 75 and tried to assign to column first_name of length 30)
    WHERE Id = id_v;
END UpdateCustomerName;

Compliant Code Example

CREATE TABLE CUSTOMERDETAILS (id number, first_name varchar2(30),landmark varchar2);

INSERT INTO CUSTOMERDETAILS (id, first_name, landmark) VALUES (1, 'Alice', 'Near H P Hospital'); 

CREATE OR REPLACE PROCEDURE GetCustomerName
IS
  customerName varchar2(30);
BEGIN
	SELECT first_name into customerName from CUSTOMERDETAILS where Id = 152; --Compliant code
END;

PROCEDURE UpdateCustomerName(id_v in number, first_name_v in nvarchar2(30), landmark_v in nvarchar2)
IS
BEGIN
    UPDATE CUSTOMERDETAILS
    SET first_name=first_name_v, landmark=landmark_v --Compliant code
    WHERE Id = id_v;
END UpdateCustomerName;