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?


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.

  • 1
    will you ever be porting integer data back to SQL Server from Oracle from those fields? – Jack says try topanswers.xyz May 28 '11 at 12:06
  • 1
    In this special case no. But generally We (the developers) always hope that the Oracle database administrator of our customers retire and we can migrate the house to SQL Server. – bernd_k May 28 '11 at 13:53

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 ;)

  • I'm not sure, if I follow you with respect to varchar. It is a rather simple text replacement. But keeping the number types in sync is a real advantage. – bernd_k May 29 '11 at 14:59
  • 1
    @bernd - I think the reference to varchar is because integer isn't an Oracle type, but it is fine to use it as they will never remove the mapping to number(38). Just like they will never remove varchar despite the long-standing threat. – Jack says try topanswers.xyz May 29 '11 at 15:09
  • I asked this question because the internet sources, didn't state explicitly that an constraint to true integers is enforced. But I checked that point. – bernd_k May 29 '11 at 15:14

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);
  • The only practical problems I encountered so far, occurred when porting back from Oracle to SQL-Server are with date to dateime when used to contain time only values. But migration back is the exception. – bernd_k May 29 '11 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.