Home

Description

The rule "Global public variables should not be defined" states that global variables should not be declared in PL/SQL code. Global variables are variables that are accessible to all parts of the code, and can be changed by any part of the code. This can lead to unexpected behavior and can make it difficult to debug and maintain code. Therefore, it is best practice to avoid using global public variables in PL/SQL code.

Key Benefits

• Security: Defining global public variables can lead to security vulnerabilities as they can be accessed and modified by any part of the code.
• Maintainability: Global public variables can lead to code that is difficult to maintain as they can be modified from anywhere in the code base.
• Readability: Global public variables can make code difficult to read as they can be used in multiple places without any indication.

Non-compliant Code Example

CREATE OR REPLACE PACKAGE CUSTOMER_PACKAGE 
IS
 TYPE CUSTOMER_T IS RECORD              --Non compliant code (Global public variable is defined)
	( FirstName VARCHAR2(50),
	  LastName VARCHAR2(50),
	  Area VARCHAR2(100),
	  City VARCHAR2(100));

	function GetCompleteCustomerDetails(customerId In CUSTOMER_T.Id) RETURN CUSTOMER_T;

END CUSTOMER_PACKAGE

Compliant Code Example

CREATE OR REPLACE PACKAGE CUSTOMER_PACKAGE 
As
	function GetCompleteCustomerDetails(customerId In INTEGER) RETURN CUSTOMER_T;

END CUSTOMER_PACKAGE;

CREATE TYPE CUSTOMER_T AS OBJECT
( FirstName VARCHAR2(50),
  LastName VARCHAR2(50),
  Area VARCHAR2(100),
  City VARCHAR2(100));

CREATE OR REPLACE PACKAGE BODY CUSTOMER_PACKAGE 
IS
 TYPE CUSTOMER_T IS RECORD              --Compliant code (Variable is not defined globally)
	( FirstName VARCHAR2(50),
	  LastName VARCHAR2(50),
	  Area VARCHAR2(100),
	  City VARCHAR2(100));
	  
	function GetCompleteCustomerDetails(customerId In INTEGER)
		return CUSTOMER_T 
		Is
		BEGIN
			Select FIRSTNAME, LASTNAME, AREA, CITY Into CUSTOMER_T.FirstName, CUSTOMER_T.LastName, CUSTOMER_T.Area, CUSTOMER_T.City FROM CUSTOMERS Where Id = customerId;
			RETURN CUSTOMER_T; 
	END GetCompleteCustomerDetails;

END CUSTOMER_PACKAGE;