How to permanently configure number format in Oracle SQL Developer?

2
votes

I am writing some queries in Oracle SQL Developer with numbers that have many digits. It would be really helpful if in general, all numbers had the thousands separator standard with no decimals.

The current default setting is: 999999999999,00

I would like this setting: 999.999.999.999

(European group and decimal separator)

I know that I can change the format of numbers using to_char, and possibly other functions.

But I do not want to have to add a function for each field in the select statement. I want to know how to just change the default number setting in Oracle SQL developer and have this format used for all queries.

1
sqloracleoracle-sqldeveloper
I think you are out of luck. There are NLS settings in sql developer, but don't think that helps in this case. write a function that takes a number and does the to_char with the format mask in the function. that way all the formatting is in one place - OldProgrammer
Hi, I do not know how to write a function like that. Having all the formatting in one place would be a big improvement. Could you please show me an example? - Haphy_Paphy

1 Answers

1
votes

have this format used for all queries.

If you mean format all queries you run in SQL Developer it's super easy.

The NLS Settings are controlled by the SQL Developer preferences sub-menu. The location of this varies; because I use a Mac for me it's on the Oracle SQL Developer menu but if you use Windows I think you'll find it on the Tools menu.

Anyway, the path is Preferences -> Database -> NLS. Then you can set the Decimal Separator and Group Separator:

screenshoot of SQL Developer NLS window

Hmmmm, just re-read your post, and realised I may have missed the point.

all numbers had the thousands separator standard with no decimals.

We can't not have a decimal separator. Oracle will default it to the default separator for the NLS Territory if nothing is specified (not sure if this is documented behaviour). The only way to avoid it would be to obviate the need for a decimal separator and store only integers in the database - or round every number you select.