What Is BIGINT Data Type in Oracle?

The BIGINT data type in Oracle is used to store large integer values. It is designed to hold whole numbers ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. This data type is commonly used when dealing with very large numbers or when the precision of a regular integer data type is not sufficient.

How to Declare a BIGINT Data Type

To declare a column with the BIGINT data type in Oracle, you can use the following syntax:

CREATE TABLE table_name (
    column_name BIGINT
);

This creates a table with a column named column_name that can store values of type BIGINT.

Working with BIGINT Data Type

The BIGINT data type supports all standard arithmetic operations such as addition (+), subtraction (-), multiplication (*), and division (/). You can perform these operations on columns or variables of type BIGINT just like any other numeric data types.

In addition to basic arithmetic operations, you can also use various built-in functions and operators specific to the BIGINT data type. These include:

• ABS(): Returns the absolute value of a BIGINT number.
• SIGN(): Returns the sign of a BIGINT number (-1 for negative numbers, 0 for zero, and 1 for positive numbers).
• MOD(): Returns the remainder after dividing one BIGINT number by another.

Example

CREATE TABLE employees (
    id BIGINT,
    name VARCHAR(100),
    salary BIGINT
);

INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 1000000);

SELECT id, name, salary FROM employees;

In the above example, we create a table named employees with three columns: id, name, and salary. The id and salary columns are of type BIGINT.

We then insert a row into the table with the values 1, ‘John Doe’, and 1000000 for the id, name, and salary columns respectively. Finally, we retrieve all rows from the employees table.

Note:

The BIGINT data type is not available in all versions of Oracle. It was introduced in Oracle 12c.

Conclusion

The BIGINT data type in Oracle allows you to store large integer values that may exceed the range of regular integer data types. It provides a higher level of precision for handling very large numbers. By using appropriate functions and operators, you can perform various mathematical operations on columns or variables of type BIGINT.

If you need to work with extremely large whole numbers in your Oracle database, consider using the BIGINT data type to ensure accuracy and efficiency.