Home » SQL & PL/SQL » SQL & PL/SQL » limit of number datatype (oracle 9i)
limit of number datatype [message #347247] Thu, 11 September 2008 03:22 Go to next message
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 #347251 is a reply to message #347247] Thu, 11 September 2008 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is a timestamp, store it in a timestamp column not a number one.
Full stop.

Regards
Michel
Re: limit of number datatype [message #347252 is a reply to message #347247] Thu, 11 September 2008 03:32 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #347265 is a reply to message #347261] Thu, 11 September 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (col number(25));

Table created.

SQL> insert into t values (11092008102124311639);

1 row created.

SQL> set numwidth 30
SQL> select * from t;
                           COL
------------------------------
          11092008102124311639

1 row selected.

Nothing is losed, just your display is wrong.

Regards
Michel
Re: limit of number datatype [message #347266 is a reply to message #347261] Thu, 11 September 2008 03:49 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
ThomasG
Messages: 3189
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.

Previous Topic: table in TEMP Tablespace
Next Topic: Pls Explain the Difference (merged)
Goto Forum:
  


Current Time: Thu Dec 08 04:08:38 CST 2016

Total time taken to generate the page: 0.07774 seconds