oracle datatypes

Datatype conversion is one of the key issues when convert one dialect of SQL to another. This article documents Oracle datatypes and how to convert it to corresponding datatype of other databases.

Oracle Database provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes.

A datatype is either scalar or nonscalar. A scalar type contains an atomic value, whereas a nonscalar (sometimes called a “collection”) contains a set of values. A large object (LOB) is a special form of scalar datatype representing a large scalar value of binary or character data.

We focused on bulit-in datatypes and ansi supported datatypes in this article. user defined datatypes and Oracle supplied types(Any Types, XML Types, Spatial Types and Media Types) were not discussed here.

bulit-in datatypes: Character

• CHAR [(size [BYTE | CHAR])]
  • Description: Fixed-length character data of length size bytes or characters.
    Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
  • ISO synonym: character, char
  • SQL Server: char[(size)], 1 <= size <= 8,000 bytes
• VARCHAR2(size [BYTE | CHAR])
  • Description: Variable-length character string having maximum length size bytes or characters.
    Maximum size is 4000 bytes or characters,and minimum is 1 byte or 1 character.
    You must specify size for VARCHAR2.
    BYTE indicates that the column will have byte length semantics.
    CHAR indicates that the column will have character semantics.
  • ISO synonym: character varying, char varying
  • Oracle synonymous:VARCHAR,To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.
  • SQL Server: varchar(size), 1 <= size <= 8,000 bytes
• NCHAR[(size)]
  • Description: Fixed-length character data of length size characters.
    The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding.
    Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes.
    Default and minimum size is 1 character.
  • ISO synonym: national character, national char, nchar
  • SQL Server: nchar[(size)]
• NVARCHAR2(size)
  • Description: Variable-length Unicode character string having maximum length size characters.
    The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding.
    Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes.
    You must specify size for NVARCHAR2.
  • ISO synonym: national character varying, national char varying, nchar varying
  • SQL Server: nvarchar(size)

bulit-in datatypes: long and raw

• long
  • Description: Character data of variable length up to 2 gigabytes, or 2^31 -1 bytes. Provided for backward compatibility.
  • ISO synonym: N/A
  • SQL Server: varchar(max)
• long raw
  • Description: Raw binary data of variable length up to 2 gigabytes.
  • ISO synonym: N/A
  • SQL Server: image/varbinary(max)
• raw(size)
  • Description: Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
  • ISO synonym: N/A
  • SQL Server: varbinary(size)

bulit-in datatypes: large object

• clob
  • Description: A character large object containing single-byte or multibyte characters.
    Both fixed-width and variable-width character sets are supported, both using the database character set.
    Maximum size is (4 gigabytes – 1) * (database block size).
  • ISO synonym: N/A
  • SQL Server: varchar(max)
• nclob
  • Description: A character large object containing Unicode characters.
    Both fixed-width and variable-width character sets are supported, both using the database national character set.
    Maximum size is (4 gigabytes – 1) * (database block size).
    Stores national character set data.
  • ISO synonym: N/A
  • SQL Server: ntext
• blob
  • Description: A binary large object. Maximum size is (4 gigabytes – 1) * (database block size).
  • ISO synonym: N/A
  • SQL Server: varbinary(max)/image
• bfile
  • Description: Contains a locator to a large binary file stored outside the database.
    Enables byte stream I/O access to external LOBs residing on the database server.
    Maximum size is 4 gigabytes.
  • ISO synonym: N/A
  • SQL Server: VARBINARY(MAX)

bulit-in datatypes: Number

• NUMBER [ (p [, s]) ]
  • Description: Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits.
    In a NUMBER column, floating point numbers have decimal precision.
    In a BINARY_FLOAT or BINARY_DOUBLE column, floating-point numbers have binary precision.
    NUMBER (*, scale), In this case, the precision is 38, and the specified scale is maintained.
  • ISO synonym: numeric[(p [, s])]
  • SQL Server:number to float; number(p [,s]) to numeric(p [,s]), if s > p, then set p = s.
• FLOAT [(p)]
  • Description:A subtype of the NUMBER datatype having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 in binary digits. A FLOAT value requires from 1 to 22 bytes.
  • ISO synonym: float
  • SQL Server: float[(p)], if p>53, then convert to float.
• BINARY_FLOAT
  • Description:32-bit floating point number. This datatype requires 5 bytes, including the length byte.
  • ISO synonym: real
  • SQL Server: float(24)
• BINARY_DOUBLE
  • Description:64-bit floating point number. This datatype requires 9 bytes, including the length byte.
  • ISO synonym: double precision
  • SQL Server: float(53)

bulit-in datatypes: Datetime and Interval Datatypes

• date
  • Description:stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
  • ISO synonym: N/A
  • SQL Server: DATETIME
• timestamp[(fractional_seconds_precision)], 0<= fractional_seconds_precision <=9
  • Description:The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values.
  • ISO synonym: N/A
  • SQL Server: DATETIME
• timestamp[(fractional_seconds_precision)] with time zone, 0<= fractional_seconds_precision <=9
  • Description:a variant of TIMESTAMP that includes a time zone region name or a a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for collecting and evaluating date information across geographic regions.
  • ISO synonym: N/A
  • SQL Server:VARCHAR(37), fetched from this article
• timestamp[(fractional_seconds_precision)] with local time zone, 0<= fractional_seconds_precision <=9
  • Description:is another variant of TIMESTAMP that includes a time zone offset in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user’s local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.
  • ISO synonym: N/A
  • SQL Server:VARCHAR(37)
• INTERVAL YEAR [(year_precision)] TO MONTH, 0<= year_precision <=9, default is 2.
  • Description:stores a period of time using the YEAR and MONTH datetime fields. This datatype is useful for representing the difference between two datetime values when only the year and month values are significant.
  • ISO synonym:N/A
  • SQL Server:N/A
• INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] , 0<= day_precision <=9, default is 2. 0<= fractional_seconds_precision <=9,default is 6.
  • Description:stores a period of time in terms of days, hours, minutes, and seconds. This datatype is useful for representing the precise difference between two datetime values.
  • ISO synonym:N/A
  • SQL Server:N/A

ANSI SQL datatypes

SQL statements that create tables and clusters can also use ANSI SQL datatypes. Oracle recognizes the ANSI SQL datatype name that differs from the Oracle Database datatype name. It converts the datatype to the equivalent Oracle datatype, records the Oracle datatype as the name of the column datatype, and stores the column data in the Oracle datatype based on the conversions shown in the table that follow.

References:

• Oracle datatypes
• Oracle datatypes to SQL Server
• Microsoft SQL Server data types to Oracle data types
• Oracle: Internal representation of the NUMBER datatype
• Internal representation of Number datatype in Oracle document