Dec Datatype in Oracle Database

by
Created student table in oracle 21c

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

Introduction to Oracle Dec datatype

The DEC data type in Oracle 21c is a fixed-point decimal data type that is used to store numerical values with a fixed number of decimal places. It is similar to the NUMBER data type, but with a few key differences.

The DEC data type provides better accuracy and precision than the NUMBER data type, as it stores the decimal point in a fixed location.

Also, check: Oracle Database vs MySQL Database

Oracle Dec datatype Syntax

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

DEC (precision, scale)

Where precision is the total number of digits that the value can contain, and scale is the number of decimal places. For example, a DEC with a precision of 5 and a scale of 2 can store values up to 999.99.

Read: Change Database Name in Oracle

Oracle Dec datatype Example

Let’s take an example and creates a table with a DEC column named decimal_value with a precision of 5 and a scale of 2.

CREATE TABLE test_table (
  id NUMBER,
  decimal_value DEC (5, 2)
);

The “decimal_value” column in this example has a DEC data type. It has a scale of 2 and a precision of 5.

Now we will insert the values into the given table 

INSERT INTO test_table (id, decimal_value) VALUES (1, 123.45);
INSERT INTO test_table (id, decimal_value) VALUES (2, 678.90);
use the dec datatype in oracle 21c
using the dec datatype in oracle 21c

The DEC data type also provides improved performance compared to the NUMBER data type, as it requires less storage space.

This is because the DEC data type only stores the necessary number of decimal places, while the NUMBER data type stores the entire value, including any unused decimal places.

Also, check: Oracle Create Sequence Tutorial

Example:

Now, we will discuss how to use the dec datatype in the SQL developer tool.

Created student table in oracle 21c
Created student table in oracle 21c

Now we will insert the values into a table

INSERT INTO students (student_id, student_marks) VALUES (8232, 324.45);
INSERT INTO students (student_id, student_marks) VALUES (9562, 289.90);
inserted the values into a student table
inserted the values into a student table

Storage and performance:

When using the DEC data type, it is important to consider the amount of precision required for your application. If a high degree of precision is not required, it may be more efficient to use the NUMBER data type, as it requires less storage space.

Read: Install sample schemas 21c in Oracle

Conclusion

In conclusion, the DEC data type in Oracle 21c is a useful data type for storing numerical values with a fixed number of decimal places.

Also, take a look at some more Oracle tutorials.