It is an interesting example that deserves to be considered since it is a guaranteed case on your exam.

Let’s consider a typical INSERT statement where everything is right and the value being inserted fits column precision and scale 

So, Precision is the total number of digits and Scale is the number of digits after the decimal point.

Precision 4, scale 2: 99.99

Precision 10, scale 0: 9999999999

Precision 8, scale 3: 99999.999

Precision 5, scale -3: 99999000

Per Oracle, Precision is the number of significant digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.

While Scale is the number of digits to the right (positive) or left (negative) of the decimal point. The scale can range from -84 to 127.

Let’s create a table with NUMBER column where precision equals 8 and scale equal 2. And then insert a value that can be described with specified precision and scale.

Let see what do we have in table t1 so far

Now, let’s continue this sequence and try to insert a value with precision 9 and scale 3, but don’t change the maximum allowed number of whole digits which is 6. The maximum allowed number of whole digits is calculated = Precision – Scale. In our case Precision is 8 while Scale is 2, means we get 6 as maximum allowed number of whole digits (digits before the dot)

Pay attention, the value that has been entered was 123456.125. As we can see it was successfully inserted despite that wrong precision was used. Let’s verify what we have inside the table now.

It may look a little unexpected because precision 9 does not correspond specified column precision which is 8, and scale 3 does not correspond specified column scale which is 2. So, why was it inserted? it happened because Oracle has inserted the value with precision 2 and not 3. So, Oracle has adjusted the value in INSERT statement with p,s=9,3 to column specified p,s=8,2. But this is not all. The value entered was rounded using standard arithmetic rules. Since initially, we were trying to insert 123456.125, we get 123456.13 inserted. So, Oracle did not reject this INSERT statement. Instead, the value was adjusted to the specified column precision and scale (8 and 2 respectively). See below

You can try to insert basically any scale, it will be adjusted anyway to 2. Let’s have an example with scale equals 5. The value to be inserted is 123456.12111

As you can see this value was also inserted and was rounded as well, This time it was rounded to 123456.12 because the original value was 123456.12111(see below )

So, the intermediate rule we have discovered up to this moment is – You can specify in INSERT statement any scale, not necessarily the scale that your column has. If not matched, Oracle will adjust it using standard math rules for rounding.

Now let’s see if we can insert any number of whole digits (before the dot) not necessarily 6. For instance, let’s try to insert a value 12345.11

yes, it was inserted. See below

Let’s try to insert now 1234567.11 as a value through INSERT statement

It won’t allow because the maximum allowed number of whole digits is 6 and we have tried to enter 7 of those.

So, the second intermediate rule is – the maximum allowed number of whole digits (digits before the dot) is equal Precision – Scale. In our case 8-2=6.