Home

Description

Encapsulating procedures and functions in packages is a best practice in PL/SQL programming. By doing so, you can group related procedures and functions together, and make them easier to find and maintain. It also allows you to control access to the procedures and functions, and to control the order in which they are loaded into memory. Additionally, it allows you to create global variables that can be used across multiple procedures and functions. Packages also help to improve performance, since all the related code is loaded into memory at once, rather than having to be loaded each time it is used.

Key Benefits

• Organization: Procedures and functions should be encapsulated in packages, allowing for better organization and easier maintenance.
• Reusability: Packages can be reused in other applications, reducing the amount of code that needs to be written.
• Security: Packages can be used to control access to sensitive data, ensuring that only authorized users can access it.
• Performance: Packages can be optimized for better performance, reducing the amount of time it takes to execute a query.

Non-compliant Code Example

function GetCompleteCustomerDetails(customerId In INTEGER)     --Non compliant code (Procedures OR functions is not encapsulated in packages)
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

Compliant Code Example

CREATE OR REPLACE PACKAGE CUSTOMER_PACKAGE             --Compliant code (Procedures OR functions is encapsulated in packages)
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 
AS
	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;