Updated March 6, 2023
Introduction to Oracle number
Oracle provides the different data types to the user; number type is one of the data types we used during application development. Basically, the oracle number data type is used to store numeric values that can be either positive or negative. Normally we have a different data type, and the value of these data types can be started with a plus or minus sign but it cannot contain commas. Sometimes we require a comma before the negative number at that time it follows another numeric expression or we can say that the minus sign acts as a subtraction operator. A decimal value contains a decimal point. The important thing in financial data means account balance must be stored numeric or decimal type value.
Syntax
Number [ (precision_digit [scale_digit] ) ]
Explanation
See oracle number data type has precision and scale digits. The precision is several digits in number data type and the range of precision digit is 1 to 38. In the scale number system digit places to the right of the decimal point and range of scale, the number is -84 to 127.
How does number type work in Oracle?
Now let’s see how the number system works in oracle as follows.
Basically, the number data type is used for precision and sale number systems but both number systems are optional in decimal digits.
The numeric data type is the super data type in numeric data types and it is an internal data type that means oracle internally performs the mathematical operation. The performance of numeric data types is slower but it is extremely portable.
Here we define numbers that are used to store the numeric value with the maximum range and precision as follows.
number
We can also define the fixed-point number by using the following statement.
number(PN, SN)
Shortdecimal: in the short decimal data type we can use 7 significant digits and the range of this data type is – (10**38) to + (10**38).
Number: in this data type we can use decimal numbers up to 38 significant digits and range of this data type id – (10**125) to (10**125).
By using Longinteger values
If value is out of range then the numeric data type returns the NA. In long integers, we don’t have overflow protection and it will return the incorrect value.
By using Number values:
When we need to use a number data type at that type we must need to specify the precision and scale. Here precision is the number of significant digits and scale is used to positive or negative numbers. The positive scale is used to define the number digit to the right of the decimal point and the negative scale is used to define the number of the digit at the left of the decimal point. The number data type is supported by the oracle database and we can use it the same as SQL.
Aliases for Number data type as follows.
In oracle, we have several aliases that we can use for defining the numeric data type as follows.
Int: it is equivalent to number (38) in numeric data type.
Decimal (p, s): it is equivalent to number (p, s) in numeric data type.
Samllint (p, s): This alias we can refer to as number (p, s) in numeric data type.
Number (p, s): This alias name is equivalent to number (p, s) in numeric data type.
Here these is all just aliases names; they are not real data types in oracle. Oracle maps these aliases internally with respective numeric data types.
Examples
Now let’s see the different examples of numeric data types as follows.
CREATE TABLE num (
no_value NUMERIC(5, 2) );
Explanation
In the above example, we use the create table statement, here we created a new table name as number_sample with attribute no_value with numeric data types as shown in the above statement. When we execute the above query then the final output we illustrate by using the following snapshot.
Now insert some records into the above table by using insert into statement as follows.
insert into num values(857.99);
Explanation
In the above example, we use to insert into statement to insert records into the num table as shown in the above statement. When we execute the above query then the final output we illustrate by using the following snapshot.
If we exceed the range of numeric data types then it shows error messages as follows.
INSERT INTO num VALUES(1000.99);
Explanation
In this example, we try to insert the large value that means out of range as shown in the above statement. But it shows an error message because of range. When we execute the above query then final output we illustrate by using the following snapshot.
In the first example, we inserted successfully because the inserted number is within the range we define for that column. But in the second example, shows error messages due to the range of data type.
In this way we can use different numeric values as per our requirement, the same example we can implement by using the alias name with a different value of precision and scale.
Rules and regulation for the number
Now let’s see the different rules and regulations of numeric data type as follows.
- Inserted numbers must be within the specified range otherwise it shows an error message that is numeric overflow or underflow error.
- Instead of numeric data type, we can use aliases.
- By using numeric data type we can store fixed-point or floating-point numbers.
- If inserted for the floating-point number we can not specify the precision or scale due to decimal point.
- Constant and variable does not allow precision and scale.
- If we don’t specify the precision value then it automatically sets default by 38 or maximum supported by our system.
Conclusion – Oracle number
We hope from this article you have understood about the Oracle number. From this article, we have learned the basic syntax of number data type and we also see different examples of the number data type. From this article, we learned how and when we use the Oracle number data type.
Recommended Articles
This is a guide to Oracle number. Here we discuss the basic syntax of number data type and we also see different examples. You may also have a look at the following articles to learn more –
