Oracle numeric Datatype

by
use the numeric datatype in oracle 21c

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

Introduction to Oracle numeric Datatype

The numeric datatype in Oracle 21c represents fixed-point numbers with precision and scale. The precision represents the total number of digits in a number, and the scale represents the number of digits to the right of the decimal point.

A numeric datatype is useful when we want to store the exact value of a number without losing any precision.

Also check: Dec Datatype in Oracle Database

Oracle numeric datatype Syntax

The syntax for creating a numeric datatype in Oracle 21c is as follows:

NUMERIC(precision, scale)

Where precision is the total number of digits in the number and scale is the number of digits to the right of the decimal point.

Example of Numeric Datatype in Oracle 21c:

Here is an example of how to create a table with a numeric datatype column in Oracle 21c:

CREATE TABLE sales (
   id INT,
   sale_date DATE,
   amount NUMERIC(10, 2)
);

In this example, we create a table named “sales” with three columns – “id”, “sale_date”, and “amount”. The “amount” column is of datatype “numeric” with a precision of 10 and a scale of 2.

Now we will insert the values into a following given table 


INSERT INTO sales (id, sale_date, amount)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 1234.56);
Using numeric datatype in oracle 21c
Using numeric datatype in oracle 21c

In this example, we insert a row into the “sales” table with an “id” of 1, a “sale_date” of January 1st, 2022, and an “amount” of 1234.56.

use the numeric datatype in oracle 21c
use the numeric datatype in oracle 21c

Now we will insert the values into the following given table

INSERT INTO employees (emp_id, Joining_date, amount)
VALUES (782, TO_DATE('2022-01-01', 'YYYY-MM-DD'), 4562.56);

select * from employees;
use the numeric datatype in oracle 21c
use the numeric datatype in oracle 21c

Read: Oracle smallint Datatype

Performance and Storage of Numeric Datatype in Oracle 21c:

  • The numeric datatype in Oracle 21c is optimized for performance and storage efficiency. It stores numbers in a fixed-length format, which makes it faster to retrieve and use in calculations. A numeric datatype uses less storage space than the equivalent character data type, which means that it is more storage-efficient.
  • Numeric datatype is also optimized for arithmetic operations such as addition, subtraction, multiplication, and division. Because it stores numbers in a fixed-length format, it does not require any conversion or rounding during these operations, which can improve performance.
  • In general, the numeric datatype is a good choice when we need to store numbers with precision and scale, and when we need to perform arithmetic operations on those numbers efficiently. However, it may not be suitable for all situations, and it is important to choose the right datatype based on the specific needs of the application.

Features of Numeric Datatype in Oracle 21c:

  • Precision and Scale: Numeric values can be stored with up to 38 digits of precision and a scale of up to 127 digits.
  • Portability: The NUMERIC datatype is platform-independent, which means that numeric values can be transferred between different platforms and applications without losing precision.
  • Performance: Numeric values are stored in a compact format, which makes them efficient for storage and processing.
  • Arithmetic Operations: Numeric values can be used in arithmetic operations, such as addition, subtraction, multiplication, and division. These operations can be performed with high precision, ensuring that the results are accurate.
  • Data Integrity: The NUMERIC datatype ensures data integrity by preventing overflow and underflow errors. This means that numeric values cannot exceed the maximum or minimum value that can be stored in the datatype.
  • Compatibility: The NUMERIC datatype is compatible with other numeric datatypes in Oracle, such as NUMBER, DECIMAL, and FLOAT.
  • Ease of Use: The NUMERIC datatype can be used in SQL statements, stored procedures, and other database objects. It is also supported by most programming languages that interact with Oracle databases.

Also check: Oracle nclob Datatype

Conclusion:

So, in this Oracle tutorial, we understood how to define and use the Numeric 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