Hi,
I would like to display large numbers with separators for the thousands, for example 1234567 would become 1,234,567. Can I arrange this with some kind of session setting or do I need to create a function to arrange this.

Thanks

If in SQL*Plus, try this:

col test form 999,999
select 123456 test from dual;

will result in:

TEST
--------
123,456

is it possible to put this in query, like this:
select format(some_field, 999,999.00) from dual
This is because I'm using Delpi as programming tool on an Oracle database.

Hi,
Try this.
SELECT TO_CHAR(sal, '999,999.00') FROM emp

Hi Jora,

For purely running scripts through SqlPlus, NoviceNo1 is quite right and you should not see any problems, but to a client side Application such as Delphi it's a different matter.

You can change session settings (google groups on borland.public.delphi for "alter session set nls" for the why's and why nots) but it is not recommended to format numbers before they arrive Delphi side.

If you do a to_char() on your number columns you *will* have later problems when a user on a different client locale runs it. You will have the same problems with dates if you force them to_char. You will also have to convert them back to int/float/date etc client side to work on them.

If you want to pass dates and number as part of a query, only ever use bind variables, these will work regardless of client locale and session nls parameters.

If you wish to display queried columns client side in a particular format, use the .TIntegerField.DisplayFormat, TDateField.DisplayFormat properties of your DataSet.

There is one exception to this, if you're using the dbExpress drivers to connect, dbExpress has a known bug where it only support american numeric characters (,.).

Do check out google, but if you've still got probs post again with more specific details of the problem and I'll try to help.

HTH
Bill