Home

Description

The rule "Output parameters should be assigned" states that when writing PL/SQL code, any output parameters should be assigned a value before the code is executed. This ensures that the output parameters are set to the correct value and will be returned correctly when the code is executed. This is important for code readability and maintainability, as it allows the programmer to clearly see what values are being returned from the code. It also helps to prevent unexpected results, as any output parameters that are not assigned a value will return a NULL value.

Key Benefits

• Ease of use: Output parameters make it easier to use functions, as they provide a way to access the data returned by the function without having to use a return statement.
• Flexibility: Output parameters can be used to pass multiple values back from a function, allowing for greater flexibility in how data is returned.
• Efficiency: Output parameters can be used to reduce the amount of code needed to retrieve data from a function, as the data can be accessed directly from the output parameter.

Non-compliant Code Example

function GetCompleteCustomerDetails(customerId In INTEGER,
FirstName out nvarchar2,            
LastName out nvarchar2,             
Area out nvarchar2,                 
City out nvarchar2)return nvarchar2         --Non compliant code (Output parameters are not assigned)
IS
BEGIN
	Select FIRSTNAME, LASTNAME, AREA, CITY Into FirstName, LastName, Area FROM CUSTOMERS;
	RETURN(CONCAT(CONCAT(ADDRESS1,ADDRESS2),CONCAT(Area,City))); 
END GetCompleteCustomerDetails

Compliant Code Example

function GetCompleteCustomerDetails(customerId In INTEGER,
FirstName out nvarchar2,            
LastName out nvarchar2,             
Area out nvarchar2,                 
City out nvarchar2)return nvarchar2         --Compliant code (Output parameters are assigned)
IS
BEGIN
	Select FIRSTNAME, LASTNAME, AREA, CITY Into FirstName, LastName, Area, City FROM CUSTOMERS;
	RETURN(CONCAT(CONCAT(ADDRESS1,ADDRESS2),CONCAT(Area,City))); 
END GetCompleteCustomerDetails