Datatypes in Oracle

Each value manipulated by Oracle Database has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype.

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

Oracle Built-in Data Types:

Oracle supports 4 datetime types:

- DATE: fixed length of 7 bytes and comprises the century, year, month, day, hour, minute and second of a date between 1 January 4712 BC and 31 December 9999 AD.

The default date values are determined as follows:

• The year is the current year, as returned by SYSDATE.
• The month is the current month, as returned by SYSDATE.
• The day is 01 (the first day of the month).
• The hour, minute, and second are all 0.

eg: SELECT TO_DATE('2017', 'YYYY') FROM DUAL;

TO_DATE
01-Jan-2017
TO_DATE function converts a character or numeric value to a date.

You can use the date format model "J" with date functions TO_DATE and TO_CHAR to convert between Oracle DATE values and their Julian equivalents. The following statement returns the Julian equivalent of January 1, 2017 :

SELECT TO_CHAR(TO_DATE('01-01-2015', 'MM-DD-YYYY'),'J') FROM DUAL;  
      TO_CHAR  
      -------  

      2457024  

- TIMESTAMP: extend the DATE data type and enable fractions of a second to be stored (with a precision of up to 9 digits) along with the year, month, day, hour and minute.
This datatype is useful for storing precise time values.

Syntax: TIMESTAMP [(fractional_seconds_precision)]

- TIMESTAMP WITH TIME ZONE: also stores the time zone region name or offset from UTC (GMT) to enable local time zone information to be preserved.

Syntax: TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

- TIMESTAMP WITH LOCAL TIME ZONE: data is converted to the database time zone when it is stored and to the time zone of the user when it is retrieved.

Syntax: TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

Oracle supports 2 interval types: 

To store a period of time rather than a specific date and time.

- INTERVAL YEAR TO MONTH: stores a period of time as years (with a precision from 0 to 9 digits) and months (with a value from 0 to 11).

Syntax: INTERVAL YEAR [(year_precision)] TO MONTH


- INTERVAL DAY TO SECOND: stores a period of time as days (with a precision from 0 to 9 digits), hours, minutes and seconds (with a fractional second precision from 0 to 9 digits).

Syntax: INTERVAL DAY [(day_precision)]  TO SECOND [(fractional_seconds_precision)]

where

• day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
• fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.

RAW and LONG RAW Datatypes:

The RAW and LONG RAW datatypes store data that is not to be interpreted (that is, not explicitly converted when moving data between different systems) by Oracle Database. These datatypes are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use.

Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns.

RAW is a variable-length datatype like VARCHAR2, except that Oracle Net (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as CB.

Large Object (LOB) Datatypes:

The built-in LOB datatypes BLOB, CLOB, and NCLOB (stored internally) and BFILE (stored externally) can store large and unstructured data such as text, image, video, and spatial data.

The size of BLOB, CLOB, and NCLOB data can be up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).

LOB columns contain LOB locators that can refer to in-line (in the database) or out-of-line (outside the database) LOB values. Selecting a LOB from a table actually returns the LOB locator and not the entire LOB value. 

•  BLOB datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics.
• The CLOB datatype stores single-byte and multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the database character set.
• The NCLOB datatype stores Unicode data.
• BFILE: 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.
  You can change the filename and path of a BFILE without affecting the base table by using the BFILENAME function.

BLOB, CLOB and NCLOB objects have full transactional support.

ANSI, DB2, and SQL/DS Data Types

Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name and it converts the data type to the equivalent Oracle data type. Following table shows the conversions :

Oracle-Supplied Types

Oracle provides some new data types which are not present in built-in or ANSI-supported types. These types can be implemented in C/C++, Java, or PL/ SQL. Here is the details :

Any Types :

The Any types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These data types let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. These types have OCI and PL/SQL interfaces for construction and access.

XML Types :

This Oracle-supplied type can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. XMLType is a system-defined type, so you can use it as an argument of a function or as the data type of a table or view column. You can also create tables and views of XMLType. When you create an XMLType column in a table, you can choose to store the XML data in a CLOB column, as binary XML (stored internally as a CLOB), or object relationally.

URI Data Types:

Oracle supplies a family of URI types—URIType, DBURIType, XDBURIType, and HTTPURIType—which are related by an inheritance hierarchy.

Spatial Types:

Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled applications, geographic information system (GIS) applications, and geoimaging applications. After the spatial data is stored in an Oracle Database, you can easily manipulate, retrieve, and relate it to all the other data stored in the database. The following data types are available only if you have installed Oracle Spatial.

Media Types:

Oracle Multimedia uses object types, similar to Java or C++ classes, to describe multimedia data. An instance of these object types consists of attributes, including metadata and the media data, and methods. The Multimedia data types are created in the ORDSYS schema. Public synonyms exist for all the data types, so you can access them without specifying the schema name. 

Oracle Multimedia provides the following object types: