Hi all,

  I am trying to create  a table (i'm new to ORACLE) and want to create a column with number data type (SAL number(8,2))

My max sal would be 99999.99(total of 8) . So should my number column be number(8,2) or number(7,2)?

When I say number(8,2), i could store 999999.99 (a total of 9). But i thought number(8,2) in oracle means total 8 places with 2 digits after decimal and 5 before?

Thanks,

SQL> CREATE TABLE t (n NUMBER(8,2));
 
Table created.
 
SQL> INSERT INTO t VALUES (1E5);
SQL> INSERT INTO t VALUES (999999.99);
SQL> INSERT INTO t VALUES (1E6);
INSERT INTO t VALUES (1E6)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
 
 
SQL> SELECT n FROM t;
 
         N
----------
    100000
 999999.99
 
SQL>

[size=3][font=Arial, Helvetica, sans-serif]NUMBER Datatype[/font][/size]

The [i]NUMBER[/i] datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10[sup]-130[/sup] and 9.9...9 x 10[sup]125[/sup] (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10[sup]126[/sup], then Oracle returns an error.

Specify a fixed-point number using the following form:
[code]NUMBER(p,s) [/code]

where:
[list][*][i][i]p[/i][/i] is the [b]precision[/b], or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.[*][i][i]s[/i][/i] is the [b]scale[/b], or the number of digits to the right of the decimal point. The scale can range from -84 to 127.[/list]

Specify an integer using the following form:
[code]NUMBER(p)[/code]

This represents a fixed-point number with precision p and scale 0 and is equivalent to [i]NUMBER(p,0)[/i].

Specify a floating-point number using the following form:
[code]NUMBER[/code]

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.