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

**(8 and 2 respectively). See below**

*scale*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.