Mapping SQL Server integer to Oracle Datatype

Question

Well I know, that an SQL Server integer is best represented by NUMBER(10).

But sometimes I have migrate some maintenance scripts to Oracle and I wonder if I must carefully map integer to NUMBER(10) or if I can just use the ansi type integer in Oracle.

Is there any reason not to use integer?

EDIT:

I verified it against 10g.

create table BK_int_test(
i_col integer,
f_col float);

insert into BK_int_test values (1, 1);
insert into BK_int_test values (1.1, 1.1);

Select * from BK_int_test;

and got

     I_COL      F_COL
---------- ----------
         1          1
         1        1,1

2 rows selected.

Thanks that saves a lot of work.

The short form int of integer is supported as well.

Answer 1

I don't see any reason to not use integer in Oracle. It will be mapped to the approriate Oracle datatype but keeps the DDL at least somewhat portable.

I also always use VARCHAR instead of VARCHAR2 as they are equivalent anyway. Oracle does not recommend this, as in the future this might change, but this has been true since version 6, so I doubt that it will actually change at all ;)

Answer 2

The only two reasons I can think of for not just using integer are:

1. If you plan on porting integer data back to SQL Server from Oracle from those fields at some point: if integers out of the range -2,147,483,648 to 2,147,483,647 have been inserted on the Oracle side, these values will overflow on the return journey
2. If some part of an application is relying on integers being in the aforementioned range. Values outside this range might therefore cause an error

Neither of these potential problems would be resolved by using number(10) which allows integers in the range -9,999,999,999 to 9,999,999,999. If it is necessary to constrain the integers to the same range, it would be better to use a check constraint, eg:

alter table my_table add check (val between -2147483648 and 2147483647);