Oracle Database - Datatype Number - Precisão máxima

Estes dias li um post muito legal do http://laurentschneider.com/ ,sempre leio bem os posts dele pois ele sempre procura coisas "escondidas" dentro do Oracle .Acho interessante a visão dele principalmente nos testes com SQL.

O datatype NUMBER tem algumas coisas interessantes para ser observadas.

Vamos lá!

[oracle@marte ~]$ sqlplus  dbajcc SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 22 13:10:53 2010 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx (xnum number(39)) ; create table jcc_numx (xnum number(39))                                    * ERROR at line 1: ORA-01727: numeric precision specifier is out of range (1 to 38) Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx (xnum number(38)) ; Table created. Elapsed: 00:00:00.03 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx ;  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  XNUM                                                           NUMBER(38) dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 38 digitos insert into jcc_numx values(10000000000000000000000000000000000000) ; dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 39 digitos insert into jcc_numx values(100000000000000000000000000000000000000); dbajcc@DBPOST on 22-FEB-10 >  2 values(100000000000000000000000000000000000000)        * ERROR at line 2: ORA-01438: value larger than specified precision allowed for this column Elapsed: 00:00:00.02 dbajcc@DBPOST on 22-FEB-10 > commit ;dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx ;       XNUM LENGTH(XNUM) ---------- ------------ 1.0000E+37           38 Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx2 (xnum number) ; Table created. Elapsed: 00:00:00.02 dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx2;  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  XNUM                                                           NUMBER dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 38 digitos insert into jcc_numx2 values(10000000000000000000000000000000000000) ; commit ; dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >--insere 39 digitos insert into jcc_numx2 values(100000000000000000000000000000000000000); commit ;dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >--insere um googol insert into jcc_numx2 values(10000000000000000000000000000000000000000000000000000000000000 000000000000000000000000000000000000000) ; commit ;dbajcc@DBPOST on 22-FEB-10 >  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx2 ;       XNUM LENGTH(XNUM) ---------- ------------ 1.0000E+37           38 1.0000E+38           39 1.000E+100           40 Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >insert into jcc_numx2 values(11111111111111111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111) ;  2 1 row created. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >select xnum,length(xnum) from jcc_numx2 ;       XNUM LENGTH(XNUM) ---------- ------------ 1.0000E+37           38 1.0000E+38           39 1.000E+100           40 1.1111E+99           40 Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >commit ; Commit complete. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >

dbajcc@DBPOST on 22-FEB-10 >create table jcc_numx3 (xnum number(*)) ; Table created. Elapsed: 00:00:00.04 dbajcc@DBPOST on 22-FEB-10 >desc jcc_numx3 ;  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  XNUM                                                           NUMBER dbajcc@DBPOST on 22-FEB-10 > dbajcc@DBPOST on 22-FEB-10 >--insere 126 casas dbajcc@DBPOST on 22-FEB-10 >insert into jcc_numx3 values(111111111111111111111111111111111111111111111111111111111111111111111 111111111111111111111111111111111111111111111111111111111) ; commit ;   2  --insere 127 casas insert into jcc_numx3 values(1111111111111111111111111111111111111111111111111111111111111111111111 111111111111111111111111111111111111111111111111111111111) ; select xnum,length(xnum) from jcc_numx3 ; 1 row created. Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 > Commit complete. Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >  2  values(11111111111111111111111111111111111111111111111111111111111111111111111 11111111111111111111111111111111111111111111111111111111)        * ERROR at line 2: ORA-01426: numeric overflow Elapsed: 00:00:00.01 dbajcc@DBPOST on 22-FEB-10 >dbajcc@DBPOST on 22-FEB-10 >       XNUM LENGTH(XNUM) ---------- ------------ 1.111E+125           40 Elapsed: 00:00:00.00 dbajcc@DBPOST on 22-FEB-10 >