Datatype Mapping Rules for Sybase to Oracle

Datatype Mapping Rules for Sybase to Oracle

The conversion of datatypes from Sybase to Oracle needs to be addressed in three steps. The first step is deciding which physical datatypes in Sybase map to which physical datatypes in Oracle, maintaining the same or greater precision. The second step is to lay out which Uniface datatype and packing codes currently are being used to generate the existing Sybase physical datatypes in existing Uniface applications. And then lastly decide if some Uniface datatype and packing codes must change to generate a physical type under Oracle that more closely matches the Sybase to Oracle datatype map from step one.

A task that is not part of conversion but is probably best tackled during conversion is cleanup of inconsistent types. For example, in some places university fiscal year is a char. But most places implement this field as int or smallint, or even numeric(4,0). If we want to make this consistent, now is the best time to do so. It’s still an open question how much we want to tackle of this cleanup during the conversion.

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<4000, else CLOB	Where possible, varchar2(n) is a better choice, as it is easier to work with. Otherwise, CLOB has the same size limits as text.
image	raw(n) for n < 2000, else BLOB	Similar comments as text.

Uniface packing code to Oracle map

Sybase Type	Uniface datatype, packing code used to generate the Sybase type	Changes required to better correspond to the above Sybase to Oracle map
smalldatetime, datetime	E,E9 D,!D D,D E,D E, E E, E7	Matches map for all Uniface codes

numeric, decimal	N,Np,s	Matches map

smallmoney, money	N,M6 N,M1 N,M2 N,M4	Only N,M4 gives 4 digits of precision; the rest give 2. *All uniface codes should be changed to N,M4*

real, float, double	F, F4 F, F4.3 F, F8	Matches map

tinyint	N,I1 N,C1	N,I1 matches map; *N,C1 should be changed to N,I1*

smallint	N,I2 N,C4 N,!C4 N,F	N,F should change to N,I2. The N,C4 and N,!C4 map to number(4) rather than the preferred number(5) of N,I2. This should not cause a problem.

int	N,I3 N,I4	N,I3 maps to number(9) instead of number(12) of N,I4. Either this should be changed, or data should be checked for type underflow during conversion.

char, varchar	Cxx VCxx	Cxx are changed to VCxx. VCxx matches the map.

bit	B,B B,C1	Matches map.

timestamp	S,VR S,VR8	S,VR should be changed to S,VR8. S,VR8 correctly maps to raw(8)

varbinary	R,R* R,VRn R,SR*	R,R* should be changed to R,SR* so long raws become BLOBs. R,VRn can stay as is for n < 256, else these should be changed to R,SR. R,SR maps to BLOB as desired.

image	R,VRn R,SR*	This is a type rarely used at IU. But for n < 256, V,VRn is preferred to give a physical type of raw. Else use R,SR* to produce a BLOB.

text	SS,SC* S,SCn	For n < 2000 use S,SCn. Note that when the Oracle 8 driver is deployed, this limit may increase to 4000. S,SCn produces a varchar2 mapping. Otherwise use SS,SC* to produce a CLOB type.

binary	R,VRn R,SR*	Like the image type, the binary type is rarely used. However, for n < 256, use R,VRn to produce a raw type; otherwise use R,SR* to produce a BLOB.

Changes Summary:

Date and datetime fields no longer hold fractions of a second. Exiting fractions of a second in Sybase will be truncated when moved to Oracle.
All char and varchar Sybase data is mapped to Oracle varchar2. Trailing whitespace is trimmed during the conversion, down to a minimum of a single blank character in the case of all whitespace entries. The Uniface driver will automatically right trim all data stored in Oracle; batch and other developers will have to take care that rtrim logic becomes part of their Oracle code.
Bit semantics have changed, as a char(1) is the best bit equivalent in Oracle. Rather than a number syntax, it is hence now a string syntax.
There is no money type in Oracle. However, numer(19,4) stores the same precision as the sybase money type. A question currently into the business side is whether just 2 places of precision to the right of the decimal is desireable. If it is, and if there is no fractions of a penny in existing money types, then this could change to number(19,2). This issue will be resolved this week.
Rather than set integer-type sizes in Oracle, Oracle lets you specify exactly how large of an integer is desired. Hence for the tinyint, smallint, and int types, the recommendation on what size number(n) to declare in Oracle is based on what the Uniface driver tends to generate for Oracle. But other than a slight increase in how large an integer can be stored in the converted integer-type fields in Oracle, no other changes affect these types.
There is no timestamp type in Oracle. The data can be converted to raw(8), but Oracle doesn’t modify the timestamp automatically as in Sybase. Hence any application logic depending on the behavior of timestamp columns will be broken. This will most likely render all timestamp columns obsolete, as most are a result of physical implementation details in Sybase with DB-lib.
Smaller text and binary data are being mapped to varchar2 and raw. For larger values, the mapping is CLOB and BLOB. Because of the restrictions on the long types, these are being avoided completely.

General Rules:

When creating a new fields in a new or existing table, check if fields by that name already exist in the IU datamodel. If so, be sure to use the same datatypes.
For e.g. decision support, if data is altered by the datagroup build process, use different field names reflect this difference.
For Uniface developers, do not pick packing codes for datatypes arbitrarily or based on default edit masks or other default form behavior based on a packing code. The packing type is "overloaded" in the sense that not only does it define default edits for the field, but it defines the physical datatype as well. Packing codes should be consistent for a given field so that the underlying datatype remains consistent. With updates to the driver for Oracle, two different packing codes that currently map to the same physical datatype may no longer do so. Hence where possible, keep the packing codes uniform.