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 ?
|
|
|
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 saySQL> 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 #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.
|
|
|
|
|