limit of number datatype [message #347247] |
Thu, 11 September 2008 03:22  |
rohitupgzp
Messages: 11 Registered: August 2008
|
Junior Member |
|
|
Hi,
In our transaction we want to store the timestamp in number format.
The table tbl has a column num number(25) and we are using below to insert data into it.
SELECT to_char(systimestamp,'ddmmyyyyhhmissFF') INTO num FROM dual ;
INSERT INTO tbl VALUES (num);
Now oracle is storing it as exponentiation(power of 10) and also it is rounding them.
How can I store the full the number in oracle.
Thanks
Rohit Sinha
|
|
|
|
Re: limit of number datatype [message #347252 is a reply to message #347247] |
Thu, 11 September 2008 03:32   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
First of all, you are trying to cram a string into your number column (to_char returns a string, not a number)
Secondly, proof that the number in the column actually is stored as exponential, and that this is not just the way your client is displaying it..
|
|
|
Re: limit of number datatype [message #347260 is a reply to message #347247] |
Thu, 11 September 2008 03:40   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> Now oracle is storing it as exponentiation(power of 10) and also it is rounding them.
Wrong. Number is stored in internal number representation; it may be displayed in exponential notation though. What does SELECT TO_CHAR(num, '999,999,999,999,999,999,999,999,999') FROM tbl show?
As mentioned before, it is not a good idea.
|
|
|
Re: limit of number datatype [message #347261 is a reply to message #347252] |
Thu, 11 September 2008 03:43   |
rohitupgzp
Messages: 11 Registered: August 2008
|
Junior Member |
|
|
Ok I will make it simple
I want to store a number 11092008102124311639 into database. Without rounding and without exp.
Currentlly when I store and do a select it gives 1.10920081021243E19
so i am loosing last five digits.
I want that when I insert and do a select , I should get
11092008102124311639
and not 1.10920081021243E19.
|
|
|
|
Re: limit of number datatype [message #347266 is a reply to message #347261] |
Thu, 11 September 2008 03:49   |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
hi,
is there any harm in storing that lengthy number in varchar2 field and retrieval. while retrieval also i did not give string embedded values. Straight away i gave number to retrive it did.
i do not know the direct solution for your question. still i circumvent some of the limitations (my knowledge) and achieved using varchar2 data type.
yours
dr.s.raghunathan
|
|
|
Re: limit of number datatype [message #347268 is a reply to message #347266] |
Thu, 11 September 2008 03:54   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
dr.s.raghunathan wrote on Thu, 11 September 2008 10:49 | is there any harm in storing that lengthy number in varchar2
|
Yes, it would be a stupid and error prone thing to do.
From a data integrity standpoint, a performance standpoint AND from a storage size standpoint.
Store data in the data types that where designed to handle that data.
|
|
|
Re: limit of number datatype [message #347271 is a reply to message #347268] |
Thu, 11 September 2008 04:03   |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
dear thomasG,
Quote: |
from a storage size standpoint
|
i agree.
Quote: |
a performance standpoint
|
i do not face or come accross on handling 25 digit number.
Also I thought while generating the apex page all items are created as text and subsequently in required form it has been converted. That is the reason, i suggested to use varchar2.
Any way thanks for correction.
yours
dr.s.raghunathan
|
|
|
Re: limit of number datatype [message #347273 is a reply to message #347271] |
Thu, 11 September 2008 04:08   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
A really conpelling argument for not storing numbers in strings would be ordering and range searching. Strings and numbers are treated very differently in these areas:SQL> create table test_0081 (col_1 number, col_2 varchar2(20));
Table created.
SQL>
SQL> insert into test_0081 select level,level from dual connect by level <= 20;
20 rows created.
SQL>
SQL> select * from test_0081 order by col_1;
COL_1 COL_2
---------- --------------------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
20 rows selected.
SQL>
SQL> select * from test_0081 order by col_2;
COL_1 COL_2
---------- --------------------
1 1
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
2 2
20 20
3 3
4 4
5 5
6 6
7 7
8 8
9 9
20 rows selected.
SQL>
SQL> select * from test_0081 where col_2 between '1' and '3';
COL_1 COL_2
---------- --------------------
1 1
2 2
3 3
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
14 rows selected.
SQL>
SQL> select * from test_0081 where col_1 between 1 and 3;
COL_1 COL_2
---------- --------------------
1 1
2 2
3 3
|
|
|
Re: limit of number datatype [message #347276 is a reply to message #347273] |
Thu, 11 September 2008 04:18   |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
hi,
if i create varchar2(25) and if i am storing number always with lpad the suggested limitations can be overcome. Is there still any other draw backs on storing 25 digits number in varchar2.
i know there will be question when there is a data type and why to struggle using lpad function and all.
still i like to know whether i am going to face problem on already used varchar2 for storing lengthy number and i had used padded zeros too.
yours
dr.s.raghunathan
|
|
|
Re: limit of number datatype [message #347281 is a reply to message #347273] |
Thu, 11 September 2008 04:25  |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Performance wise:
When you store a number as a number then no implicit/explicit conversions are needed when you use it as a number.
When you store it as a varchar then implicit/explicit conversions are needed. Which might under certain circumstances even prevent the use of an index.
Example where the INDEX (RANGE SCAN) changes to an TABLE ACCESS (FULL):
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> CREATE TABLE test_tab (col NUMBER(12));
Table created.
SQL> CREATE INDEX test_idx ON test_tab(col);
Index created.
SQL>
SQL> INSERT INTO test_tab (SELECT ROWNUM
FROM all_objects
WHERE ROWNUM < 100);
99 rows created.
SQL>
SQL> SET autotrace ON
SQL> SELECT * FROM test_tab WHERE col = 3;
COL
----------
3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
219 bytes sent via SQL*Net to client
276 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET autotrace OFF
SQL>
SQL>
SQL> DROP TABLE test_tab;
Table dropped.
SQL> CREATE TABLE test_tab (col varchar2(12));
Table created.
SQL> CREATE INDEX test_idx ON test_tab(col);
Index created.
SQL>
SQL> INSERT INTO test_tab (SELECT To_Char(ROWNUM)
FROM all_objects
WHERE ROWNUM < 100);
99 rows created.
SQL>
SQL> SET autotrace ON
SQL> SELECT * FROM test_tab WHERE To_Number(col) = 3;
COL
------------
3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST_TAB'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
220 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET autotrace OFF
SQL>
SQL> DROP TABLE test_tab;
Table dropped.
|
|
|