Oracle SQL Arithmetic Operators and Numeric Data Types: A Complete Guide to Calculation Basics and NULL Traps

Introduction: To Obtain Accurate Calculation Results
Conclusion/Shortest Path (To-Do List)
Background and Basics: Deep Dive into Numeric Data Types and Operators
Procedures and Implementation: Practical Calculation with the SCOTT Schema
Troubleshooting: Common Errors and Workarounds
1. ORA-01722 and the Horror of Implicit Type Conversion
Operational, Monitoring, and Security Considerations
1. 1. Rounding Policy for Amount Calculations
2. 2. Notes on Format Conversion
FAQ (Frequently Asked Questions)
Summary

Introduction: To Obtain Accurate Calculation Results

In system development using Oracle Database, amount calculations and aggregation processes are unavoidable. However, troubles such as “calculation results becoming NULL in production data even though it worked in the test environment,” “a 1-yen discrepancy occurring in consumption tax calculations,” or “batch processes stopping due to unexpected overflow errors” never cease.

These are not merely bugs, but are fundamentally caused by a lack of correct understanding regarding the specifications of the Oracle numeric data type (NUMBER) and the behavior of NULL based on three-valued logic (True/False/Unknown).

In this article, we will not only cover the usage of basic operators but also thoroughly explain the strict behavior of Precision and Scale in the NUMBER type, essential practical NULL countermeasures using NVL / COALESCE, and best practices for type conversion with performance in mind, all from a professional perspective.

This article will explain using an example using the SCOTT sample schema.
The SCOTT sample schema can be imported and used by running the following:

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger

Conclusion/Shortest Path (To-Do List)

When implementing numeric calculations, strictly adhering to the following checklist can prevent rework and production failures.

Thorough NULL Countermeasures
- If there is no NOT NULL constraint on the column to be calculated, always use the NVL function or standard SQL COALESCE to convert NULL to a calculable value (usually 0).
- Discard the assumption that “data should be there” and enforce defensive programming.
Operator Precedence and Readability
- Multiplication and division (* /) take precedence over addition and subtraction (+ -).
- Actively use parentheses () to clarify intent, regardless of precedence rules. This also prevents future modification errors.
Strict Data Type Definitions
- Explicitly specify the number of digits for amounts and quantities using NUMBER(p, s).
- Specifically, by specifying s (scale), you control automatic rounding during DB storage and prevent unintended discrepancies on the application side.
Defense Against Zero Division
- When performing division, consider the possibility of the denominator being 0 and handle it using DECODE, CASE expressions, or the NULLIF function.

Background and Basics: Deep Dive into Numeric Data Types and Operators

Detailed Specifications of the NUMBER Type

The Oracle NUMBER type is a variable-length numeric data type characterized by its ability to handle a very wide range of numbers with high precision.

Syntax: NUMBER(p, s)

p (precision): The total number of digits that can be stored (1 to 38). Does not include the sign or decimal point.
s (scale): The number of digits to the right of the decimal point (-84 to 127).

Differences in Behavior by Definition (Important)

The behavior of the NUMBER type varies significantly depending on the specification of p and s. Being ambiguous here leads to ORA-01438 errors and arbitrary rounding.

Definition | Stored Data Example: 123.456 | Explanation of Behavior

—|—|—

NUMBER(10, 2) | 123.46 | Rounded to the 2nd decimal place and stored.

NUMBER(5) | 123 | When scale is omitted, it is treated as 0 and stored as an integer (rounded).

NUMBER(3) | Error | The integer part is 3 digits, but since it exceeds the precision of 3 digits (after attempting to store the rounded value), it cannot be stored (details below).

NUMBER | 123.456 | If unspecified, it is stored as is with maximum precision.

Important Point: The rounding process in the NUMBER(p, s) definition is performed automatically during data INSERT or UPDATE. Note that this is ROUND (round half up), not TRUNC (truncate).

Arithmetic Operators and Modulo Operations

In addition to standard arithmetic operations, modulo (remainder) operations are also frequently used.

Operator	Name	Usage Example	Result	Remarks
+	Addition	`10 + 5`	`15`	Addition to date types is also possible (adding days).
–	Subtraction	`10 - 5`	`5`	Subtraction between dates is also possible (calculating duration).
*	Multiplication	`10 * 5`	`50`
/	Division	`10 / 5`	`2`	Even with integers, the result may be a decimal.
MOD	Modulo	`MOD(10, 3)`	`1`	Provided as a function.

Procedures and Implementation: Practical Calculation with the SCOTT Schema

We will explain more practical and complex calculation cases using the EMP table (employee table) of the sample schema (SCOTT).

Prerequisite Environment: Oracle Database 19c / SCOTT schema installed

1. Confirming Basic Operations and Data Types (Dual Table)

First, we retrieve calculation results of constants from the database. Here we also check the handling of decimals and negative numbers.

SELECT
    100 + 20     AS add_calc,
    100 - 200    AS sub_calc_negative,
    100 * 1.08   AS tax_calc,
    100 / 3      AS div_calc_infinite
FROM
    dual;

[Explanation]

sub_calc_negative: The result is -100; Oracle handles negative numbers without issues.
div_calc_infinite: The result is 33.333..., displayed up to the precision limit. In business operations, it is common to handle fractions with ROUND or TRUNC.

SQL> SELECT
  2      100 + 20     AS add_calc,
  3      100 - 200    AS sub_calc_negative,
  4      100 * 1.08   AS tax_calc,
  5      100 / 3      AS div_calc_infinite
  6  FROM
  7      dual;

  ADD_CALC SUB_CALC_NEGATIVE   TAX_CALC DIV_CALC_INFINITE
---------- ----------------- ---------- -----------------
       120              -100        108        33.3333333

2. Salary Calculation: The NULL Trap and Countermeasures

Scenario: Calculate the “Total Payment” by adding the employee’s “Monthly Salary (SAL)” and “Commission (COMM)”.

In Oracle, NULL indicates a state where the “value does not exist (Unknown)”. The grand principle of SQL is “Adding anything to an unknown value results in unknown (NULL).”

❌ Failure Example: Simple Addition

SELECT
    EMPNO, ENAME, SAL, COMM,
    SAL + COMM AS TOTAL_WRONG
FROM
    EMP
WHERE
    COMM IS NULL;

[Explanation]

TOTAL_WRONG becomes NULL for all rows. This is fatal for a payroll calculation system.

SQL> SELECT
  2      EMPNO, ENAME, SAL, COMM,
  3      SAL + COMM AS TOTAL_WRONG
  4  FROM
  5      EMP
  6  WHERE
  7      COMM IS NULL;

     EMPNO ENAME             SAL       COMM TOTAL_WRONG
---------- ---------- ---------- ---------- -----------
      7369 SMITH             800
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

10 rows selected.

⭕ Success Example: Defend with NVL or COALESCE

Use Oracle’s proprietary NVL function or the SQL standard COALESCE function.

SELECT
    EMPNO, ENAME, SAL, COMM,
    SAL + NVL(COMM, 0) AS TOTAL_NVL,
    SAL + COALESCE(COMM, 0) AS TOTAL_COALESCE
FROM
    EMP
WHERE
    SAL + NVL(COMM, 0) >= 2000;

[Explanation]

TOTAL_NVL: Uses NVL(COMM, 0). This is the traditional Oracle way; if COMM is NULL, it returns 0.
TOTAL_COALESCE: Uses COALESCE(COMM, 0). This is the ANSI standard and can take multiple arguments. It can be used similarly to NVL, but is sometimes recommended in modern SQL development.

SQL> SELECT
  2      EMPNO, ENAME, SAL, COMM,
  3      SAL + NVL(COMM, 0) AS TOTAL_NVL,
  4      SAL + COALESCE(COMM, 0) AS TOTAL_COALESCE
  5  FROM
  6      EMP
  7  WHERE
  8      SAL + NVL(COMM, 0) >= 2000;

     EMPNO ENAME             SAL       COMM  TOTAL_NVL TOTAL_COALESCE
---------- ---------- ---------- ---------- ---------- --------------
      7566 JONES            2975                  2975           2975
      7654 MARTIN           1250       1400       2650           2650
      7698 BLAKE            2850                  2850           2850
      7782 CLARK            2450                  2450           2450
      7788 SCOTT            3000                  3000           3000
      7839 KING             5000                  5000           5000
      7902 FORD             3000                  3000           3000

7 rows selected.

3. Annual Income Calculation and Precedence in Complex Expressions

Scenario: Calculate annual income. “12 months of Basic Salary” plus “Commission”, and further add a flat “Special Allowance of 500”.

SELECT
    EMPNO,
    ENAME,
    SAL * 12 + NVL(COMM, 0) + 500 AS BAD_STYLE,
    (SAL * 12) + NVL(COMM, 0) + 500 AS ANNUAL_INCOME
FROM
    EMP
ORDER BY
    ANNUAL_INCOME DESC;

[Explanation]

BAD_STYLE: A bad example. The precedence of operators is unclear, and the reader cannot instantly understand “where calculation starts.”
ANNUAL_INCOME: A good example. By adding parentheses like (SAL * 12), the business logic of “first calculate annual basic salary, then add other allowances” is clearly conveyed.

SQL> SELECT
  2      EMPNO,
  3      ENAME,
  4      SAL * 12 + NVL(COMM, 0) + 500 AS BAD_STYLE,
  5      (SAL * 12) + NVL(COMM, 0) + 500 AS ANNUAL_INCOME
  6  FROM
  7      EMP
  8  ORDER BY
  9      ANNUAL_INCOME DESC;

     EMPNO ENAME       BAD_STYLE ANNUAL_INCOME
---------- ---------- ---------- -------------
      7839 KING            60500         60500
      7902 FORD            36500         36500
      7788 SCOTT           36500         36500
      7566 JONES           36200         36200
      7698 BLAKE           34700         34700
      7782 CLARK           29900         29900
      7499 ALLEN           20000         20000
      7844 TURNER          18500         18500
      7654 MARTIN          16900         16900
      7934 MILLER          16100         16100
      7521 WARD            16000         16000
      7876 ADAMS           13700         13700
      7900 JAMES           11900         11900
      7369 SMITH           10100         10100

14 rows selected.

4. Safe Avoidance of Zero Division (Using NULLIF)

Scenario: Calculate the commission rate, determining what percentage the “Commission (COMM)” is of the “Salary (SAL)”.

However, we must consider the possibility that data with SAL as 0 exists (e.g., during a leave of absence).

SELECT
    EMPNO,
    ENAME,
    SAL,
    COMM,
    ROUND(NVL(COMM, 0) / NULLIF(SAL, 0) * 100, 2) AS COMM_PCT
FROM
    EMP;

[Explanation]

If you divide by SAL directly, an error occurs when SAL is 0.
NULLIF(SAL, 0): Returns NULL if SAL is 0.
Since the result of “Number / NULL” is NULL in SQL, this avoids the zero division error (ORA-01476) and safely treats it as NULL (incalculable).

SQL> SELECT
  2      EMPNO,
  3      ENAME,
  4      SAL,
  5      COMM,
  6      ROUND(NVL(COMM, 0) / NULLIF(SAL, 0) * 100, 2) AS COMM_PCT
  7  FROM
  8      EMP;

     EMPNO ENAME             SAL       COMM   COMM_PCT
---------- ---------- ---------- ---------- ----------
      7369 SMITH             800                     0
      7499 ALLEN            1600        300      18.75
      7521 WARD             1250        500         40
      7566 JONES            2975                     0
      7654 MARTIN           1250       1400        112
      7698 BLAKE            2850                     0
      7782 CLARK            2450                     0
      7788 SCOTT            3000                     0
      7839 KING             5000                     0
      7844 TURNER           1500          0          0
      7876 ADAMS            1100                     0
      7900 JAMES             950                     0
      7902 FORD             3000                     0
      7934 MILLER           1300                     0

14 rows selected.

Troubleshooting: Common Errors and Workarounds

We delve into typical errors occurring in arithmetic operations and their root causes.

Error Code	Error Message	Detailed Cause and Professional Workaround
(None)	Result becomes NULL	Cause: Specification of three-valued logic. If even one `NULL` is mixed in, the result is `NULL`. Workaround: Strictly use `NVL` / `COALESCE`. Be especially careful with table columns after joins.
ORA-01476	divisor is equal to zero	Cause: Occurrence of mathematically undefined “zero division.” Workaround: Use `DECODE(col, 0, NULL, col)` or `NULLIF(col, 0)` for the denominator to design it to return `NULL` instead of an error.
ORA-01438	value larger than specified precision allowed for this column	Cause: Insertion of a value exceeding the defined number of digits. Example: Attempting to insert `1000` into a `NUMBER(3)` column. Workaround: Review the design of the number of digits. If it is a temporary workaround, changing the column definition (`ALTER TABLE ... MODIFY ...`) is necessary.
ORA-01722	invalid number	Cause: Implicit type conversion failure. Example: Attempting to calculate a numeric column with a string `'1,000'` (with comma), etc. Workaround: Explicitly convert using `TO_NUMBER` with a format specification.

ORA-01722 and the Horror of Implicit Type Conversion

In SQL, if you compare a string type column (STR_COL) with a number like WHERE STR_COL = 100, Oracle internally executes TO_NUMBER(STR_COL) = 100 (implicit type conversion).

If there is even one record with data like ‘ABC’ in STR_COL, the query will crash with an ORA-01722 error the moment that row is evaluated. Also, since a function is executed on the column, indexes are not used, causing a full scan and drastically reducing performance.

Lesson: Always match types. If comparing strings, writing WHERE STR_COL = '100' is the iron rule.

Operational, Monitoring, and Security Considerations

1. Rounding Policy for Amount Calculations

In financial systems and e-commerce sites, rules for rounding (truncation, rounding up/down) may be legally determined.

Do not rely on the automatic rounding (round half up) performed when storing into the Oracle NUMBER type; you should use explicit functions in SQL.

TRUNC(col, 0): Truncate decimal places (frequently used in consumption tax calculations, etc.)
ROUND(col, 0): Round half up
CEIL(col): Ceiling (round up to integer)

2. Notes on Format Conversion

When converting numbers to comma-separated strings for screen display, use TO_CHAR.

SELECT TO_CHAR(1234567, '999,999,999') AS FMT_VAL FROM dual;

[Explanation]

The result is ' 1,234,567'.
Note that a space for the sign is automatically inserted at the beginning.
Do not use the result of TO_CHAR for further calculations (it causes ORA-01722).

SQL> SELECT TO_CHAR(1234567, '999,999,999') AS FMT_VAL FROM dual;

FMT_VAL
------------
   1,234,567

FAQ (Frequently Asked Questions)

Q1. How do I get the “remainder” of a division?

Use the MOD function.

SELECT MOD(10, 3) FROM dual;

[Explanation]: The result is 1 (10 ÷ 3 = 3 with a remainder of 1). It is often used for determining odd/even numbers (MOD(n, 2)) or distributing data into N groups.

SQL> SELECT MOD(10, 3) FROM dual;

 MOD(10,3)
----------
         1

Q2. What happens if I omit the digit specification for the NUMBER type?

If defined simply as NUMBER, it is stored with the maximum precision supported by Oracle (38 digits precision, no scale limit).

Pros: No error even if digits increase in the future.

Cons: Risk of unexpected huge numbers or unintended decimals being input on the application side.

Basically, explicit definitions like NUMBER(10) or NUMBER(12, 2) are recommended to clarify design intent.

Q3. Are NULL and 0 the same?

No, they are completely different in the database.

0: Holds information as the numeric value “zero”. Can be used for calculation.
NULL: A state of “value not entered,” “unknown,” or “not applicable.” If calculated, it drags the result into NULL.Aggregate functions (SUM, AVG) ignore NULL, but arithmetic operations (+ *) do not ignore it.

Q4. Can I use the positive sign (+) or negative sign (-) as unary operators?

Yes, you can.

SELECT -SAL FROM EMP;

Writing this retrieves the salary value inverted to a negative number (-1 times). It is used for expressing deficits in accounting processes, etc.

SQL> SELECT -SAL FROM EMP;

      -SAL
----------
      -800
     -1600
     -1250
     -2975
     -1250
     -2850
     -2450
     -3000
     -5000
     -1500
     -1100
      -950
     -3000
     -1300

14 rows selected.

Summary

Understand the meaning of the Oracle numeric data type NUMBER(p,s) (integer part and decimal part) when defining it.
When using arithmetic operators, never forget that calculation results with NULL will always be NULL.
Always perform calculations after converting NULL to a number using NVL(column_name, 0).
In practice, also beware of zero division (ORA-01476) and overflow (ORA-01438).

This article explains concepts based on Oracle Database 19c (screens and default values may differ in other versions).