NUmber Data Type

Test Case 1: NUMBER (no precision or scale)

CREATE TABLE test_number_1 (val NUMBER);

INSERT INTO test_number_1 (val) VALUES (123.89);

SELECT * FROM test_number_1;

-- Output: 123.89

 

Test Case 2: NUMBER(3)

CREATE TABLE test_number_2 (val NUMBER(3));

INSERT INTO test_number_2 (val) VALUES (123.89);

SELECT * FROM test_number_2;

-- Output: 124

-- Explanation: Rounded to fit in 3 digits (no scale), 123.89 → 124

 

Test Case 3: NUMBER(3,2)

CREATE TABLE test_number_3 (val NUMBER(3,2));

INSERT INTO test_number_3 (val) VALUES (123.89);

-- ORA-01438: value larger than specified precision allowed for this column

 

Test Case 4: NUMBER(4,2)

CREATE TABLE test_number_4 (val NUMBER(4,2));

INSERT INTO test_number_4 (val) VALUES (123.89);

-- ORA-01438: value larger than specified precision allowed for this column

 

Test Case 5: NUMBER(5,2)

CREATE TABLE test_number_5 (val NUMBER(5,2));

INSERT INTO test_number_5 (val) VALUES (123.89);

SELECT * FROM test_number_5;

-- Output: 123.89

 

Test Case 6: NUMBER(6,1)

CREATE TABLE test_number_6 (val NUMBER(6,1));

INSERT INTO test_number_6 (val) VALUES (123.89);

SELECT * FROM test_number_6;

-- Output: 123.9

-- Explanation: Rounded to 1 decimal place

 

Test Case 7: NUMBER(6,-2)

CREATE TABLE test_number_7 (val NUMBER(6,-2));

INSERT INTO test_number_7 (val) VALUES (123.89);

SELECT * FROM test_number_7;

-- Output: 100

-- Explanation: Rounded to nearest hundred

 

Test Case 8: .01234 into NUMBER(4,5)

CREATE TABLE test_8 (val NUMBER(4,5));

INSERT INTO test_8 VALUES (.01234);

SELECT val FROM test_8;

-- Output: 0.01234

 

Test Case 9: .00012 into NUMBER(4,5)

CREATE TABLE test_9 (val NUMBER(4,5));

INSERT INTO test_9 VALUES (.00012);

SELECT val FROM test_9;

-- Output: 0.00012

 

Test Case 10: .000127 into NUMBER(4,5)

CREATE TABLE test_10 (val NUMBER(4,5));

INSERT INTO test_10 VALUES (.000127);

SELECT val FROM test_10;

-- Output: 0.00013

-- Explanation: Rounded to 5 decimal places.

 

Test Case 11: .0000012 into NUMBER(2,7)

CREATE TABLE test_11 (val NUMBER(2,7));

INSERT INTO test_11 VALUES (.0000012);

SELECT val FROM test_11;

-- Output: 0.0000012

 

Test Case 12: .00000123 into NUMBER(2,7)

CREATE TABLE test_12 (val NUMBER(2,7));

INSERT INTO test_12 VALUES (.00000123);

SELECT val FROM test_12;

-- Output: 0.0000012

-- Explanation: Rounded to 2 significant digits.

 

Test Case 13: 1.2e-4 into NUMBER(2,5)

CREATE TABLE test_13 (val NUMBER(2,5));

INSERT INTO test_13 VALUES (1.2e-4);

SELECT val FROM test_13;

-- Output: 0.00012

 

Test Case 14: 1.2e-5 into NUMBER(2,5)

CREATE TABLE test_14 (val NUMBER(2,5));

INSERT INTO test_14 VALUES (1.2e-5);

SELECT val FROM test_14;

-- Output: 0.00001

-- Explanation: Rounded to 2 significant digits.