G-9020

🆓

Warning

Try to use a format model and NLS_NUMERIC_CHARACTERS in string to number conversion functions.

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 5 mins
Quick Fix | Local-medium
Database Support: OracleDB (v7.3.4 +)

Reason

Converting from strings to numeric datatypes (using to_number, to_binary_double, to_binary_float or cast to any of those datatypes) rely on session NLS settings for nls_numeric_characters. Typically the input string is expected to have a given decimal- and group-separator, so it is best practice to specify nls_numeric_characters in the function call. However, this requires also setting a format model, which is a good idea but can require a very large format model with many 9's if you do not know the maximum length of the string.

To avoid an inappropriate dependability on configurable NLS parameters, try to use both format model and nls_numeric_characters in the conversion function call. The exceptions can be if the input is known to always be integer with no decimal- or group-separator, or if you do not know the maximum number of digits and have control over the session nls_numeric_characters parameter.

Example

Non-Compliant Example

create or replace package body employee_api is
   procedure set_salary(
      in_employee_id in employees.employee_id%type
     ,in_salary      in varchar2
   ) is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_salary      constant type_up.date_string        := in_salary;
   begin
      update employees
         set salary = to_number(co_salary default null on conversion error)
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/

Issues

Line	Column	Message
10	23	Missing format model and NLS_NUMERIC_CHARACTERS in to_number call.

Compliant Solution - ★★★★★

create or replace package body employee_api is
   procedure set_salary(
      in_employee_id in employees.employee_id%type
     ,in_salary      in varchar2
   ) is
      co_employee_id constant employees.employee_id%type := in_employee_id;
      co_salary      constant type_up.date_string        := in_salary;
   begin
      update employees
         set salary = to_number(
                co_salary default null on conversion error
               ,'9999999999999999999999999999D9999999999'
               ,q'[nls_numeric_characters='.,']'
             )
       where employee_id = co_employee_id;
   end set_dob;
end employee_api;
/

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
IntegerPlaces	Number of digits before the decimal point. Used in the quick fix for the number of 9's in format model.	28
DecimalCharacter	Decimal character used in the quick fix for the nls_numeric_characters parameter.	.
DecimalPlaces	Number of digits after the decimal point. Used in the quick fix for the number of 9's in format model.	10
ThousandSeparator	Thousand separator (group separator) used in the quick fix for the nls_numeric_characters parameter.	,
DisableAllQuickFix	Comma-separated ist of rules for which a quick fix should not be applied to all the problems in a file.	Core G-3130

Rule Details

Reason

Example

× Non-Compliant Example

✓ Compliant Solution - ★★★★★

Parameters

References

Non-Compliant Example

Compliant Solution - ★★★★★