Unable to set precision for INTEGER data type in SQL CREATE TABLE command

Question

I am trying to create the following table in Oracle.

CREATE TABLE CUSTOMER(CUST_ID INT(10),
CUST_NAME   VARCHAR2(50),
CUST_SEX    CHAR(2),
CUST_STATE  VARCHAR2(50),
CUST_COUNTRY    VARCHAR2(50));

I get an error saying that the right parenthesis is missing. In reality, the issue is with the INT data type for the CUST_ID column. Once I remove the precision :(10) from the DDL query, I am able to execute it successfully.

Oracle docs don't specify anything with regarding to whether this data type can be accompanied by a precision parameter or not. However Oracle does mention that INTEGER/INT is per ANSI standards.

https://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_datatypes002.htm

Certain other non-official references describe INT/INTEGER to be a synonym for NUMBER(38).

Can someone please tell me if precision cannot indeed be specified for INT datatype?

Answer 1

The Oracle docs state that:

SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type

As the table below that sentence states, int, integer, and (surprisingly?) smallint are all synonyms for number(38), so you cannot specify a precision for them. For your usecase, if you want an integer number with ten digits, you should use number(10).

Answer 2

As the documentation says, INTEGER is equivalent to NUMBER(38).

You can just use INTEGER where you want to store integers of any size, or you can use NUMBER(n) if you want to constrain the number of digits in the values to n.

Note: the only reason for specifying the precision in Oracle is for validation and documentation. There is no space advantage in using smaller values of n: the value 123456 occupies the same number of bytes in NUMBER(6) and NUMBER(38) and INTEGER columns - i.e. 4 bytes.

Answer 3

Let me try: precision cannot indeed be specified for INT datatype.

How did it sound?

Documentation says:

<snip>
| { NUMERIC | DECIMAL | DEC } [ (precision [, scale ]) ]  --> precision + scale
| { INTEGER | INT | SMALLINT }                            --> no precision for integers
| FLOAT [ (size) ]
<snip>

Answer 4

The INT[EGER] data type (which should be , at least mostly, a 4-byte binary integer), in Oracle, exists, if at all, in PL/SQL stored procedures.

Your best bet is to design a NUMBER(5) for a SMALLINT, a NUMBER(9) for an INTEGER, and a NUMBER(18) for a LARGEINT/BIGINT

If you go:

CREATE TABLE dropme (i INT);

, in Oracle, you get a table with a column i NUMBER (with no length specification, which boils down to a pretty in-efficient NUMBER(38).

The Oracle numeric data types are NUMBER , with an optional overall precision and an optional decimal scale, and FLOAT.

And an Oracle NUMBER, at least as I understood it, is a variable-length construct, with a binary, two-byte, length indicator for the whole thing, followed by a binary decimal notation , in which every following half-byte can hold between 0000 and 1001, binary, or 0 to 9 - except the last one, which contains the sign: positive/negative.