OraFAQ Forum: SQL & PL/SQL » Set Num 40 in oracle sqlplus

Home » SQL & PL/SQL » SQL & PL/SQL » Set Num 40 in oracle sqlplus

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

Set Num 40 in oracle sqlplus [message #27149]

Wed, 27 August 2003 01:21

Sunil
Messages: 132
Registered: September 1999

Senior Member

Sometimes if the number is too long e.g

222222222222
it is shown in sql plus on selecting ,

2.22222E10,

but when we use set num 40 in sqlplus prompt, it shows it correctly. I wanted to know whether the number is stored as 2222222222 only acutally in the database or as 2.222222E10. and while using comparision operator like say in the WHERE statement, do i have to make a to_number conversion ?

Report message to a moderator

Re: Set Num 40 in oracle sqlplus [message #27151 is a reply to message #27149]

Wed, 27 August 2003 06:54

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Sunil,

The full number is stored in the database. Your seeing 2.222222E10 is determined by your SQL*Plus (client) settings, not by database (server) storage.

See here and here for more about how Oracle stores NUMBERs.

This SQL should help clear your doubts:

SQL> SHOW NUMWIDTH
numwidth 10
SQL> CREATE TABLE t (n NUMBER);
  
Table created.
  
SQL> INSERT INTO t VALUES (12345678901234567890);
  
1 row created.
  
SQL> COMMIT;
  
Commit complete.
  
SQL> SELECT n
  2  FROM   t
  3  /
  
         N
----------
1.2346E+19
  
SQL> SELECT n
  2  FROM   t
  3  WHERE  n BETWEEN 12345678901234567800
  4               AND 12345678901234567900
  5  /
  
         N
----------
1.2346E+19
  
SQL> SET NUMWIDTH 40
SQL> SELECT n
  2  FROM   t
  3  WHERE  n BETWEEN 12345678901234567800
  4               AND 12345678901234567900
  5  /
  
                                       N
----------------------------------------
                    12345678901234567890
  
SQL>

See, no TO_NUMBER is required.

You know, Oracle also interprets 1.2346E+19 as a numeric literal. So you can also say

SQL> SHOW NUMWIDTH
numwidth 40
SQL> SELECT n
  2  FROM   t
  3  WHERE  n BETWEEN 1.2345E19
  4               AND 1.2346E19
  5  /
  
                                       N
----------------------------------------
                    12345678901234567890
  
SQL> SELECT n
  2  FROM   t
  3  WHERE  n BETWEEN 2E+7
  4               AND 9E+30
  5  /
  
                                       N
----------------------------------------
                    12345678901234567890
  
SQL>

HTH, Sunil.

Art.

Report message to a moderator

Re: Set Num 40 in oracle sqlplus [message #27153 is a reply to message #27151]

Wed, 27 August 2003 11:53

adelia
Messages: 45
Registered: April 2001

Member

How can I insert a value like 1.6800E-02 on a column type number ??
It number come from a access database.
The column doesn�t accept the E-02 value...

Report message to a moderator

Re: Set Num 40 in oracle sqlplus [message #27157 is a reply to message #27153]

Wed, 27 August 2003 12:02

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_{quote from ade:}
----------------------------------------------------------------------

How can I insert a value like 1.6800E-02 on a column type number ?? 
It number come from a access database.
The column doesn�t accept the E-02 value...

----------------------------------------------------------------------
Really? Works for me:

SQL> CREATE TABLE t (n NUMBER);
  
Table created.
  
SQL> INSERT INTO t VALUES (1.6800E-02);
  
1 row created.
  
SQL> COMMIT;
  
Commit complete.
  
SQL> SELECT n
  2  FROM   t
  3  /
  
         N
----------
     .0168
  
SQL>

HTH,

A.

Report message to a moderator