In the following discussion, the mapping of data types from source to destination is grouped according to the destination data type:
Source | Destination | Mapping |
---|---|---|
Character | Character | Data is left-justified and truncated or padded with spaces on the right, as needed. |
Numeric | Character | Data is right-justified with leading zeros, as needed. Leading spaces are used, as needed, for floats. Destination column must be large enough to hold significant portion of the source numeric value; otherwise error results. |
Binary | Character | Data is left-justified and truncated
or padded with spaces on the right, as needed. There is no conversion. Note: You
cannot map a binary source column to a multi-byte or Unicode destination.
|
Date/Time | Character | Fixed date/time format applies, based on source column. (See fixed formats, below.) |
Source | Destination | Mapping |
---|---|---|
Character | Numeric | Character data must be valid as numeric data. Destination column must be large enough to hold source value; otherwise, error results. Decimal places can be dropped. |
Numeric | Numeric | Destination column must be large enough to hold significant portion of the source value; otherwise, error results. Truncate decimals, if needed. |
Binary | Numeric | Binary data is transformed to numeric. Destination column must be large enough to hold source value; otherwise, error results. Decimal places can be dropped. |
Source | Destination | Mapping |
---|---|---|
Character | Binary | Data is left-justified and truncated
or padded with NULL on the right, as needed. There is no conversion. Note: You
cannot map a multi-byte or Unicode source column to a binary destination.
|
Numeric | Binary | Destination column must be large enough to hold significant portion of source value; otherwise, error results. Data is right‑justified with leading zeros for integers and decimals. Leading spaces are used as needed. |
Binary | Binary | Data is left-justified and truncated or padded with NULL on the right, as needed. |
Source | Destination | Mapping |
---|---|---|
Character | Boolean | Data is mapped from the first character. If the first character is 'T' , 'Y' , or ‘1', translate to TRUE. If the first character is 'F' , 'N', or ‘0', translate to FALSE. |
Numeric | Boolean | A zero numeric value translates to FALSE. A non-zero numeric value translates to TRUE. |
Binary | Boolean | Data is mapped from the first character. If the first character is 'T' , 'Y' , or ‘1', translate to TRUE. If the first character is 'F' , 'N', or ‘0', translate to FALSE. |
Boolean | Boolean | Equal |
Source | Destination | Mapping |
---|---|---|
Date | Date | Equal |
Time | Date | Not Supported |
Timestamp | Date | Use date portion only. |
Timestamp w/Time Zone | Date | Use date portion only; drop time zone. |
Timestamp w/Local Time Zone | Date | Use date portion only. |
Date | Time | Not Supported |
Time | Time | Equal |
Timestamp | Time | Use time portion only. |
Timestamp w/Time Zone | Time | Use time portion only; drop time zone. |
Timestamp w/Local Time Zone | Time | Use time portion only. |
Date | Timestamp | Use source date and midnight. |
Time | Timestamp | Use source time and current date. |
Timestamp | Timestamp | Equal |
Timestamp w/Time Zone | Timestamp | Use timestamp portion; drop time zone. |
Timestamp w/Local Time Zone | Timestamp | Equal |
Date | Timestamp w/Time Zone |
Use source date, midnight, and “+0”. |
Time | Timestamp w/Time Zone |
Use source time, current date, and “+0”. |
Timestamp | Timestamp w/Time Zone |
Use timestamp and “+0”. |
Timestamp w/Time Zone |
Timestamp w/Time Zone |
Equal |
Timestamp w/Local Time Zone |
Timestamp w/Time Zone |
Use timestamp with “+0”. |
Date | Timestamp w/Local Time Zone |
Use source date and midnight. |
Time | Timestamp w/Local Time Zone |
Use source time and current date. |
Timestamp | Timestamp w/Local Time Zone |
Equal |
Timestamp w/Time Zone |
Timestamp w/Local Time Zone |
Use timestamp portion; drop time zone portion. |
Timestamp w/Local Time Zone |
Timestamp w/Local Time Zone |
Equal |
For example, for a Timestamp with Local Time Zone column, Oracle normalizes the time stored in the database to the session time of the Oracle server. When Oracle retrieves this data, the stored value is adjusted by the difference between the server session time and the client session time. However, Optim™ returns the value stored in the database without making adjustments.
Therefore, it is recommended that you map Timestamp with Time Zone columns and Timestamp with Local Time Zone columns to columns of the same date type only.
The following are various special registers or functions for putting the current date and/or time into a Destination column. They may be used interchangeably.
CURRENT DATE | CURRENT TIMESTAMP | GETDATE |
CURRENT_DATE | CURRENT_TIMESTAMP | GETTIME( ) |
CURRENT TIME | CURDATE | NOW( ) |
CURRENT_TIME | CURTIME( ) | SYSDATE |
The following are special registers for putting a User ID into a Destination column. Note that they do not provide the same value.