0

Friends, i'm new in SQL and i can't find answer to my question

I'm trying to insert decimal number with SQL Developer (version 4, build 15), but SQL Developer automaticaly rounding my number. My test DB is OracleXE112_Win64.

Example:

CREATE TABLE salespeople
  (
    snum  INTEGER NOT NULL PRIMARY KEY,
    sname CHAR(15) NOT NULL,
    city  CHAR(15) NOT NULL,
    comm  DECIMAL
  );

then

insert into SALESPEOPLE 
values (1001, 'Peel', 'London', .12);
insert into SALESPEOPLE 
values (1002, 'Serres', 'San Jose', .13);
insert into SALESPEOPLE 
values (1004, 'Motika', 'London', .11);
insert into SALESPEOPLE 
values (1007, 'Rifkin', 'Barcelona', .15);
insert into SALESPEOPLE 
values (1003, 'Axelrod', 'New York', .10);

As as result i see 0 in comm column. When i'm trying to insert number 1.35, it's rounding to 1.

Thanks

Kos
  • 41
  • 1
  • 2
  • 8
  • The scale defaults to 0 for DECIMAL, which means that DECIMAL(*) is treated as NUMBER(*, 0), i.e. INTEGER – Karthik Jan 13 '17 at 13:23
  • 1
    I don't think `CHAR(15)` is the proper data type for `sname` and `city`, see [What is the major difference between VARCHAR2 and CHAR](http://stackoverflow.com/questions/20417845/what-is-the-major-difference-between-varchar2-and-char/20418849) – Wernfried Domscheit Jan 13 '17 at 13:25
  • If you are new to Oracle SQL then I suggest some basic reading (it wont take long), a starter is https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements.htm. – BriteSponge Jan 13 '17 at 13:32

2 Answers2

1

The decimal data type needs a scale and precision. "Scale" is the number of digits after the decimal point.

When you don't specify the value for scale, then it defaults to 0 -- essentially an integer. (And precision defaults to 5.)

Typically, Oracle databases use number instead of decimal. But, if you want decimal, specify the appropriate scale and precision.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Following up on Gordon's excellent answer - my recommendation, based on years of bitterly hard-won experience, is that the default numeric type to use in Oracle is NUMBER. Other numeric types, or even NUMBER with precision and/or scale specified, should only be used in situations with very unusual requirements. YMMV. – Bob Jarvis - Слава Україні Jan 13 '17 at 13:49
  • Adding my voice to this - agreeing with both Gordon and Bob. There is no reason I can think of to use DECIMAL over NUMBER. Especially since NUMBER is specifically a "decimal" numeric type (where division by 25 is 100% accurate, unlike any binary data type where it can't be). –  Jan 13 '17 at 14:16
  • Thank you very much! For learning i'm using old book by Martin Gruber. He used DECIMAL very often. Now i will use NUMBER type for this column. – Kos Jan 13 '17 at 15:38
  • @Kos - excellent! Be advised that what you will read in the book will often be different from what you find in the real world (but often the difference will be small, especially if you "know what to do" - as will be the case now with DECIMAL vs. NUMBER). In the future, feel free to post questions here, just like you did, showing what you expected and what happened. It always helps to state that you are learning from the book, and the book says x but you found y. That will help people give you better answers, knowing the background. –  Jan 13 '17 at 15:48
1

Checking the Oracle documentation for DECIMAL, it says:

"If the scale is not specified, the default scale is 0. If the precision is not specified, the default precision is 5."

Precision is : "the total number of digits, both to the left and the right of the decimal point".

Scale is : "the number of digits of the fractional component".

This means that because your default scale is 0, then all numbers are rounded to an integer.

To fix this you need to specify a SCALE for your data. Most scenarios I've seen are sufficed by DECIMAL(18,5) and from your example queries this should be fine.

So, just change your table definition to:

CREATE TABLE salespeople
  (
    snum  INTEGER NOT NULL PRIMARY KEY,
    sname CHAR(15) NOT NULL,
    city  CHAR(15) NOT NULL,
    comm  DECIMAL(18,5)
  );
Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • @Karthik Not really the point of this question / answer. Maybe OP actually requires this. – Radu Gheorghiu Jan 13 '17 at 13:27
  • Thank you very much! For learning i'm using old book by Martin Gruber. He used DECIMAL very often. Now i will use NUMBER type for this column. – Kos Jan 13 '17 at 15:38