1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle number datatype inconsistency

Discussion in 'SQL PL/SQL' started by pguser1982, Apr 8, 2014.

  1. pguser1982

    pguser1982 Guest

    Hi All,
    I am new to oracle and want to ask a basic question regarding one inconsistency I have found during query execution.

    According to datatype documentation of oracle.
    Number datatype has precision and scale when we can specify number without them also.

    In the documentation it says:

    Table 26-1 How Scale Factors Affect Numeric Data Storage
    Input Data Specified As Stored As
    ------------------------------------------------------
    7,456,123.89 NUMBER 7456123.89

    SQL> create table t_num(col1 number);

    Table created.

    SQL> insert into t_num values (10.0);

    1 row created.

    SQL> insert into t_num values (10);

    1 row created.

    SQL> insert into t_num values (1);

    1 row created.

    SQL> insert into t_num values (111121212.90);

    1 row created.

    SQL> desc t_num
    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    COL1 NUMBER

    SQL> select * from t_num;

    COL1
    ----------
    10
    10
    1
    111121213

    But:
    Example 2
    SQL> create table t_int(col1 int);

    Table created.

    SQL> insert into t_int values (9);

    1 row created.

    SQL> insert into t_int values (10);

    1 row created.

    SQL> desc t_int
    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    COL1 NUMBER(38)

    SQL> select col1, percentile_cont(0.5) within group (order by col1) over() from t_int;

    COL1 PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYCOL1)OVER()
    ---------- --------------------------------------------------
    9 9.5
    10 9.5

    SQL> create table t_int2 as select col1, percentile_cont(0.5) within group (order by col1) over() as col2 from t_int;

    Table created.

    SQL> desc t_int2
    Name Null? Type
    ----------------------------------------- -------- ----------------------------

    COL1 NUMBER(38)
    COL2 NUMBER

    SQL> select * from t_int2;

    COL1 COL2
    ---------- ----------
    9 9.5
    10 9.5

    So I can see for the same datatype number values gets converted to integer and sometimes remain decimal.

    So does anyone know the reason?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.


    Processing happens as it is necessary

    Code (SQL):


    clear scr
    DROP TABLE t_num purge;
    DROP TABLE t_int2 purge;
    DROP TABLE t_int purge;
    CREATE TABLE t_num(col1 NUMBER);
    INSERT INTO t_num VALUES (10.0);
    INSERT INTO t_num VALUES (10);
    INSERT INTO t_num VALUES (1);
    INSERT INTO t_num VALUES (111121212.90);
    DESC t_num
    SELECT to_char(col1) col1_txt , col1 FROM t_num;
    CREATE TABLE t_int(col1 INT);
    INSERT INTO t_int VALUES (9);
    INSERT INTO t_int VALUES (10);
    DESC t_int
    SELECT to_char(col1) col1_txt, col1 , percentile_cont(0.5) WITHIN GROUP (ORDER BY col1) OVER()  col2
    FROM t_int;
    CREATE TABLE t_int2 AS SELECT col1, percentile_cont(0.5) WITHIN GROUP (ORDER BY col1) OVER()  AS col2 FROM t_int;
    DESC t_int2
    SELECT * FROM t_int2;


    SQL>

    TABLE created
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted
     
    1 ROW inserted

    Name TYPE   NULLABLE DEFAULT Comments
    ---- ------ -------- ------- --------
    COL1 NUMBER Y                        
     
    COL1_TXT                                       COL1
    ---------------------------------------- ----------
    10                                               10
    10                                               10
    1                                                 1
    111121212,9                              111121212.
     
    TABLE created
     
    1 ROW inserted
     
    1 ROW inserted
    Name TYPE    NULLABLE DEFAULT Comments
    ---- ------- -------- ------- --------
    COL1 INTEGER Y                        
     
    COL1_TXT                                                                    COL1       COL2
    ---------------------------------------- --------------------------------------- ----------
    9                                                                              9        9.5
    10                                                                            10        9.5
     
    TABLE created
    Name TYPE    NULLABLE DEFAULT Comments
    ---- ------- -------- ------- --------
    COL1 INTEGER Y                        
    COL2 NUMBER  Y                        
     
                                       COL1       COL2
    --------------------------------------- ----------
                                          9        9.5
                                         10        9.5


     

    Oracle recommends to use explicit type conversions.
    there can be different settings of sessions (sessions) proceeding from local needs.
    In the majority a case, implicit conversions lead to errors, excess calls of functions and etc.