Mapping Sybase to Oracle types Sybase datatype

Oracle Datatype

Comments

smalldatetime

date

See below comments for Sybase datetime

datetime

date

Sybase records thousandths of seconds, Oracle to just seconds. Any finer granularity beyond seconds is lost (truncated) during conversion. This is not believed to be a problem. The oracle DATE type spans a wider range of days (Jan 1 4712 BCE to Jan 1 4712 CE), so no other conversion problems should occur.

numeric(p,s)

number(p,s)

The numeric and decimal types are the only non-integer numerics in Sybase guaranteed to be portable across platform for rounding, etc., behavior. All Oracle number types are portable. The numeric and decimal types in Sybase map exactly to number(p,s) in Oracle.

decimal(p,s)

number(p,s)

See above comments for Sybase numeric.

smallmoney

number(19,4)

Money in Sybase stores to 4 digits of precision to the right of the decimal point, and to approx +/-$214k for smallmoney, approx +/-$922T for money to the left of the decimal. The initial concensus from accounting department is 2 digits right of the decimal is sufficient; however, until data can be confirmed to contain no "partial pennies", four digits will be used to ensure easy data migration and reconciliation.

money

number(19,4)

See above comments for Sybase smallmoney.

real

number

Unlike Sybase, the Oracle number type is portable across platforms. Otherwise, this type corresponds to the Oracle number type.

float

number

See above comments for Sybase float.

double

number

See above comments for Sybase double.

tinyint

number(5)

Sybase tinyint ranges from 0-255. Though number(5) is slightly larger than is necessary, it is a better match for Uniface and hence helps maintain some consistency.

smallint

number(7)

Sybase smallint ranges from –32768 to +32767. Just as with tinyint and number(5), number(7) for smallint is slightly larger than necessary, but again for better consistency with Uniface.

int

number(12)

Sybase int ranges from –2^31 to 2^31 (approx +/- 2 billion). Similar comments to those from smallint, tinyint apply here.

char(n)

varchar2(n)

The max of n in Sybase is 255; in Oracle 8 it is 4000. Sybase blank pads not nullable char data and right trims nullable char data. Oracle stores precisely what is sent for varchar2. Hence in the conversion process, all trailing whitespace will be removed from Sybase data. If a

column is all whitespace, a single whitespace (a space) will remain, since an empty string in Oracle is interpreted as NULL. (Or in Perl-speak, if ( $col =~/\s+$/ ) { $col=~s/\s+$/ / } ) varchar(n)

varchar2(n)

The max of n in Sybase is 255; in Oracle 8 it is 4000. Sybase right trims all white space from varchar data. Oracle stores precisely what is sent for varchar2. Hence in the conversion process, all trailing whitespace will be removed from Sybase data. If a column is all whitespace, a single whitespace ( a space character) will remain, since an empty string in Oracle is interpreted as NULL. (Or in Perl-speak, if ( $col =~/\s+$/ ) { $col=~s/\s+$/ / } )

bit

char(1)

There is no bit type in Oracle. Uniface maps bit types to char data, so using char(1) will keep this consistent. Note this changes the semantics and some operations possible with bit fields. Specifically, literal strings in where clauses must now be quoted to be consistent with char syntax (e.g. "...where bit_field = ‘0’" instead of "... where_bit_field = 0"), and bit operations (logical and, or, etc) can no longer be safely applied.

timestamp

raw(8)

Timestamps do not exist in Oracle, and hence if converted, applications which depend on the automatically updated timestamp column cannot continue to use this logic. However, the data itself can be mapped perfectly to a raw(8).

varbinary(n)

raw(n)

In Sybase, n must be less than 256. In Oracle, n can be up to 2000. Otherwise, the type mapping is exact. For "spooled data" (i.e. – large amounts of binary data stored in varbinary fields ordered by a sequence number) consider moving to a BLOB type rather than maintaining this peculiar detail of Sybase physical implementation.

binary(n)

raw(n)

Similar comments to varbinary.

text

varchar2(n) for n