Home » SQL & PL/SQL » SQL & PL/SQL » Set Num 40 in oracle sqlplus
Set Num 40 in oracle sqlplus [message #27149] Wed, 27 August 2003 01:21 Go to next message
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 ?
Re: Set Num 40 in oracle sqlplus [message #27151 is a reply to message #27149] Wed, 27 August 2003 06:54 Go to previous messageGo to next message
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.
Re: Set Num 40 in oracle sqlplus [message #27153 is a reply to message #27151] Wed, 27 August 2003 11:53 Go to previous messageGo to next message
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...
Re: Set Num 40 in oracle sqlplus [message #27157 is a reply to message #27153] Wed, 27 August 2003 12:02 Go to previous messageGo to next message
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.
Re: Set Num 40 in oracle sqlplus [message #27158 is a reply to message #27151] Wed, 27 August 2003 22:07 Go to previous messageGo to next message
Sunil
Messages: 132
Registered: September 1999
Senior Member
Thanks a lot, Art. That was indeed pretty helpfull.
Re: Set Num 40 in oracle sqlplus [message #27166 is a reply to message #27157] Thu, 28 August 2003 07:04 Go to previous message
adelia
Messages: 45
Registered: April 2001
Member
Thanks a lot HTH.
I did the test you sugest, and work perfectly ,the problem was another one...
I lost many times thinking on it.
Previous Topic: Using Analytic Function in PL/SQL
Next Topic: Copy Schema
Goto Forum:
  


Current Time: Fri Jul 25 17:03:02 CDT 2025