G-1050: Avoid using literals in your code.

Minor

Changeability

Reason

Literals are often used more than once in your code. Having them defined as a constant reduces typos in your code and improves the maintainability.

All constants should be collated in just one package used as a library. If these constants should be used in SQL too it is good practice to write a deterministic package function for every constant.

In specific situations this rule could lead to an extreme plethora of constants, for example if you use Logger like logger.append_param(p_params =>l_params, p_name => 'p_param1_todo', p_val => p_param1_todo);, where the value for p_name always should be the name of the variable that is passed to p_val. For such cases it would be overkill to add constants for every single variable name you are logging, so if you use Logger or similar, consider making that an exception to the rule, just document exactly which exceptions you will allow and stick to them.

Example (bad)

declare
   l_job employees.job_id%type;
begin
   select e.job_id
     into l_job
     from employees e
    where e.manager_id is null;

   if l_job = 'AD_PRES' then
      null;
   end if;
exception
   when no_data_found then 
      null; -- handle_no_data_found;
   when too_many_rows then 
      null; -- handle_too_many_rows; 
end;
/

Example (good)

create or replace package constants_up is
   co_president constant employees.job_id%type := 'AD_PRES';
end constants_up;
/

declare
   l_job employees.job_id%type;
begin
   select e.job_id
     into l_job
     from employees e
    where e.manager_id is null;

   if l_job = constants_up.co_president then
      null;
   end if;
exception
   when no_data_found then 
      null; -- handle_no_data_found;
   when too_many_rows then 
      null; -- handle_too_many_rows; 
end;
/