OraFAQ Forum: SQL & PL/SQL » alter session number format

Home » SQL & PL/SQL » SQL & PL/SQL » alter session number format (oracle standard 11g, red hat)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

alter session number format [message #486829]

Mon, 20 December 2010 08:52

alreadynight
Messages: 5
Registered: December 2010

Junior Member

Hi,
anyone knows how to change the default format of a number value using alter session statement?

I've a problem when I show a value like this "0.123456": the select statement returns ".123456".
Is there any way to force a zero value before the character separator?
Thanks

Report message to a moderator

Re: alter session number format [message #486830 is a reply to message #486829]

Mon, 20 December 2010 08:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there any way to force a zero value before the character separator?
TO_CHAR() function

Report message to a moderator

Re: alter session number format [message #486834 is a reply to message #486829]

Mon, 20 December 2010 09:17

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Who (which tool/application) displays the number?
If SQL*Plus, use COLUMN statement.

Regards
Michel

Report message to a moderator

Re: alter session number format [message #486835 is a reply to message #486834]

Mon, 20 December 2010 09:37

alreadynight
Messages: 5
Registered: December 2010

Junior Member

I make a query by PHP.
It's the same on sqldeveloper.
Thanks

Report message to a moderator

Re: alter session number format [message #486836 is a reply to message #486830]

Mon, 20 December 2010 09:48

alreadynight
Messages: 5
Registered: December 2010

Junior Member

BlackSwan wrote on Mon, 20 December 2010 15:53

>
TO_CHAR() function

thank you for the response, but I don't want to change the format type of a field, but I only want to change the display format.
What I'm searching is a global configuration for all my schema, not just for one field.

Any other suggestions?
Thanks

Report message to a moderator

Re: alter session number format [message #486837 is a reply to message #486835]

Mon, 20 December 2010 09:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1* select to_char(.123,'0.999') VALUE from dual
SQL> /

VALUE
------
 0.123

Report message to a moderator

Re: alter session number format [message #486838 is a reply to message #486837]

Mon, 20 December 2010 10:13

alreadynight
Messages: 5
Registered: December 2010

Junior Member

Thank you BlackSwan, but your example change the type from number to char: I don't want to change it!
Any other tips?

Report message to a moderator

Re: alter session number format [message #486839 is a reply to message #486838]

Mon, 20 December 2010 10:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Any other tips?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: alter session number format [message #486840 is a reply to message #486835]

Mon, 20 December 2010 10:35

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

alreadynight wrote on Mon, 20 December 2010 16:37

I make a query by PHP.
It's the same on sqldeveloper.
Thanks

From PHP you can display the number (which comes from Oracle or anything) as you want, it is just a matter of CLIENT program not Oracle.

Regards
Michel

[Updated on: Mon, 20 December 2010 10:35]

Report message to a moderator

Re: alter session number format [message #486842 is a reply to message #486840]

Mon, 20 December 2010 10:44

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

As Michel said, this should be handled by the client, not the database, as it is how something is displayed, not how it is stored. There isn't any magical "alter session set ..." that will do this from Oracle. If SQL*Plus were your client, then you could do as shown below, using a SQL*Plus SET command. This is a SQL*Plus command, not an Oracle SQL or PL/SQL command. This will only affect how the data is displayed from SQL*Plus, not from other programs that access the database. There should be something similar in whatever you are using to display your data instead of SQL*Plus.

SCOTT@orcl_11gR2> select 0.123456 from dual
  2  /

0.123456
--------
 .123456

1 row selected.

SCOTT@orcl_11gR2> set numformat 0.999999
SCOTT@orcl_11gR2> select 0.123456 from dual
  2  /

 0.123456
---------
 0.123456

1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator