Oracle SQL Precision and Scale

Precision and Scale are two basic parts of Oracle number data type, and almost every Oracle SQL developer will encounter it. But recently I was confused on how much exactly Oracle SQL can store when giving precision and scale.

There are topics on SOF ( and also the definitions are given in Oracle document (

But one point I couldn’t find anywhere (maybe I didn’t notice in document), is that when exactly Oracle DB will throw error when storing numbers.

I had thought since Precision is number of total digits in a number and Scale is number of digits that right to decimal point, if the Precision is violated, i.e., when total number of digits more than defined Precision, Oracle will throw out error.

But it turns out that both Precision and Scale alone are not used to determine the error. It actually only throw out error when:
Number of Significant Digits (usually digits to the left of decimal point) > Precision – Scale

So if a number type defined as Number(8,2), so we have below scenario:

123456.12 Valid Stored as 123456.12
123456.123 Valid Stored as 123456.12
1234567 Invalid

Similarly, for type Number(8,-2):

1234567890 Valid Stored as 1234567800
123456.123 Valid Stored as 123400
12345678901 Invalid

Another interesting thing is that if you define the type as Number only, without giving Precision and Scale, Oracle will automatically take maximum precision which is 38, and use flexible scale based on input. But once you give the Precision, the scale will automatically becomes 0. So Number(38) is same as Number(38,0).

Oracle SQL Developer Password Storage

I have forgotten my password to one of our Oracle database server due to “change password every three months” policy. Luckily I have it stored in my Oracle SQL Developer application. I know the password will be stored in a encrypted format but since it must be sent in plain text to server I’m sure the encryption is reversible.

Google it giving me a few interesting result. The first is a Oracle SQL Developer extension to reveal password ( I’ve installed it but somehow it’s not always working. The extension option is not always available in right-click menu, and also it does not show all the stored passwords.

Another result points me to a gist (, which has the code to decrypt the password.

Below code is copied from the gist, credit belongs to its original author.