• Article

Data Type Mapping for Oracle Publishers

Oracle data types and Microsoft SQL Server data types do not always match exactly. Where possible, the matching data type is selected automatically when publishing an Oracle table. In cases that a single data type mapping is not clear, alternative data type mappings are provided. For information about how to select alternative mappings, see the "Specifying Alternative Data Type Mappings" section later in this topic.

The following table shows how data types are mapped by default between Oracle and SQL Server when data is moved from the Oracle Publisher to the SQL Server Distributor. The Alternatives column indicates whether alternative mappings are available.

Oracle data type

SQL Server data type

Alternatives

BFILE

VARBINARY(MAX)

Yes

BLOB

VARBINARY(MAX)

Yes

CHAR([1-2000])

CHAR([1-2000])

Yes

CLOB

VARCHAR(MAX)

Yes

DATE

DATETIME

Yes

FLOAT

FLOAT

No

FLOAT([1-53])

FLOAT([1-53])

No

FLOAT([54-126])

FLOAT

No

INT

NUMERIC(38)

Yes

INTERVAL

DATETIME

Yes

LONG

VARCHAR(MAX)

Yes

LONG RAW

IMAGE

Yes

NCHAR([1-1000])

NCHAR([1-1000])

No

NCLOB

NVARCHAR(MAX)

Yes

NUMBER

FLOAT

Yes

NUMBER([1-38])

NUMERIC([1-38])

No

NUMBER([0-38],[1-38])

NUMERIC([0-38],[1-38])

Yes

NVARCHAR2([1-2000])

NVARCHAR([1-2000])

No

RAW([1-2000])

VARBINARY([1-2000])

No

REAL

FLOAT

No

ROWID

CHAR(18)

No

TIMESTAMP

DATETIME

Yes

TIMESTAMP(0-7)

DATETIME

Yes

TIMESTAMP(8-9)

DATETIME

Yes

TIMESTAMP(0-7) WITH TIME ZONE

VARCHAR(37)

Yes

TIMESTAMP(8-9) WITH TIME ZONE

VARCHAR(37)

No

TIMESTAMP(0-7) WITH LOCAL TIME ZONE

VARCHAR(37)

Yes

TIMESTAMP(8-9) WITH LOCAL TIME ZONE

VARCHAR(37)

No

UROWID

CHAR(18)

No

VARCHAR2([1-4000])

VARCHAR([1-4000])

Yes

Considerations for Data Type Mapping

Keep the following data type issues in mind when replicating data from an Oracle database.

Unsupported Data Types

The following data types are not supported; columns that have these types cannot be replicated:

  • Object types

  • XML types

  • Varrays

  • Nested tables

  • Columns that use REF

The DATE Data Type

Dates in SQL Server range from 1753 A.D. to 9999 A.D., whereas dates in Oracle range from 4712 B.C. to 4712 A.D. If a column of type DATE contains values that are out of range for SQL Server, select the alternative data type for the column, which is VARCHAR(19).

FLOAT and NUMBER Types

The scale and precision specified during the mapping of FLOAT and NUMBER data types depends upon the scale and precision specified for the column using the data type in the Oracle database. Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

Oracle allows numbers to be defined with a scale greater than the precision, such as NUMBER(4,5), but SQL Server requires the precision to be equal to or greater than the scale. To ensure there is no data truncation, if the scale is greater than the precision at the Oracle Publisher, the precision is set equal to the scale when the data type is mapped: NUMBER(4,5) would be mapped as NUMERIC(5,5).

Note

If you do not specify a scale and precision for NUMBER, SQL Server defaults to using the maximum scale (8) and precision (38). We recommend that you set a specific scale and precision in Oracle for better storage and performance when the data is replicated.

Large Object Types

Oracle supports up to 4 gigabytes (GB), whereas SQL Server supports up to 2 GB. Data replicated above 2 GB is truncated.

If an Oracle table includes a BFILE column, the data for the column is stored in the file system. The replication administrative user account must be granted access to the directory in which the data is stored using the following syntax:

GRANT READ ON DIRECTORY <directory_name> TO <replication_administrative_user_schema>

For more information about large objects types, see the section "Considerations for Large Objects" in Design Considerations and Limitations for Oracle Publishers.

Specifying Alternative Data Type Mappings

Typically, the default data type mapping is appropriate, but for many Oracle data types, you can select a data type mapping from a set of alternative mappings, rather than using the default. There are two ways to specify alternative mappings:

  • Override the default on a per-article basis using stored procedures or the New Publication Wizard.

  • Globally change the default for all future articles using stored procedures (defaults are not changed for existing articles).

To specify alternative data type mappings