Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
will you ever be porting integer data back to SQL Server from Oracle from those fields? –  Jack Douglas 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

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer
    
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 Douglas 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);
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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