Decimal Datatype in Oracle Database

by

In this Oracle tutorial, we will learn about the decimal datatype in the oracle database. Also, we will illustrate how to use it to create decimals columns for a table in oracle.

Introduction to Oracle Decimal datatype

A decimal data type with fixed precision and scale is called DECIMAL in Oracle 21c. It is used to hold decimal numbers. It is comparable to the NUMBER data type but has a lower range and higher precision.

Oracle Decimal datatype Syntax

Here is the syntax for declaring a DECIMAL column in a table is as follows:

DECIMAL (precision, scale)
  • It consists of two main parameters
    • precision: The total number of digits that the number can contain, including the digits to the right of the decimal point. The precision can range from 1 to 38.
    • scale: The number of digits to the right of the decimal point. The scale can range from -84 to 127.

Also, check: How to check database status in Oracle

Oracle Decimal datatype Example

Let’s take an example and creates a table with a DECIMAL column named price with a precision of 6 and a scale of 2.

CREATE TABLE sales (
  product_id NUMBER,
  price DECIMAL (6, 2)
);

The “price” column in this example has a DECIMAL data type. It has a scale of 2 and a precision of 6. This indicates that it can store a total of 6 digits: 2 to the right and 4 to the left of the decimal.

Now we will insert the values in a given table

INSERT INTO sales VALUES (67, 2.45);
decimal datatype in oracle
decimal datatype in oracle

Now, we will discuss how to use the float datatype in the SQL developer tool. First, we will create a table of students and use the decimal datatype for it.

decimal datatype in sql developer
decimal datatype in SQL developer

Now we will insert the values into a students name table

INSERT INTO students VALUES (45, 87.23);
value inserted into a table in oracle
value inserted into a table in oracle

Read: Oracle Create Sequence Tutorial

Storage and performance:

The DECIMAL data type utilizes a fixed-point binary representation, therefore the precision determines how many bytes are needed to hold a DECIMAL value. More bytes will be needed to store the value the more precision you need.

In terms of performance, the DECIMAL data type may be slower than the NUMBER data type because it requires more storage and has a more complex internal representation.

Read: How to add a column to a table in Oracle

Conclusion

So, in this Oracle tutorial, we understood how to define and use the Decimal Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.