An Overview of Oracle Datatypes-Datatypes-2

•\ TIMESTAMP WITH TIME ZONE: This is a fixed-width 13-byte date/ time datatype, but it also provides for TIME ZONE support. Additional information regarding the time zone is stored with the TIMESTAMP in the data, so the TIME ZONE originally inserted is preserved with the data.

•\ TIMESTAMP WITH LOCAL TIME ZONE: This is a fixed-width 7- or 11-­ byte date/time datatype (depending on the precision), similar to the TIMESTAMP; however, it is time zone sensitive. Upon modification inthe database, the TIME ZONE supplied with the data is consulted, and the date/time component is normalized to the local database time zone. So, if you were to insert a date/time using the time zone US/ Pacific and the database time zone was US/Eastern, the final date/ time information would be converted to the Eastern time zone and stored as a TIMESTAMP. Upon retrieval, the TIMESTAMP stored in the database would be converted to the time in the session’s time zone.

•\ INTERVAL YEAR TO MONTH: This is a fixed-width 5-byte datatype that stores a duration of time, in this case as a number of years and months. You may use intervals in date arithmetic to add or subtract a period of time from a DATE or the TIMESTAMP types.

•\ INTERVAL DAY TO SECOND: This is a fixed-width 11-byte datatype that stores a duration of time, in this case as a number of days and hours, minutes, and seconds, optionally with up to nine digits of fractional seconds.

•\ BLOB: This datatype permits for the storage of up to (4 gigabytes – 1) * (database block size) bytes of data in Oracle. BLOBs contain “binary” information that is not subject to character set conversion. This would be an appropriate type in which to store a spreadsheet, a word processing document, image files, and the like.

•\ CLOB: This datatype permits for the storage of up to (4 gigabytes –1) * (database block size) bytes of data in Oracle. CLOBs contain information that is subject to character set conversion. This would be an appropriate type in which to store large plain text information. Note that I said large plain text information; this datatype would not be appropriate if your plain text data is 4000 bytes or less—for that you would want to use the VARCHAR2 datatype.

•\ NCLOB: This datatype permits for the storage of up to (4 gigabytes – 1) * (database block size) bytes of data in Oracle. NCLOBs store information encoded in the national character set of the database and are subject to character set conversions just as CLOBs are.

•\ BFILE: This datatype permits you to store an Oracle directory object(a pointer to an operating system directory) and a file name in a database column and to read this file. This effectively allows you to access operating system files available on the database server in a read-only fashion, as if they were stored in the database table itself.

•\ ROWID: A ROWID is effectively a 10-byte address of a row in a database. Sufficient information is encoded in the ROWID to locate the row on disk, as well as identify the object the ROWID points to (the table and so on).

•\ UROWID: A UROWID is a universal ROWID and is used for tables—such as IOTs and tables accessed via gateways to heterogeneous databases— that do not have fixed ROWIDs. The UROWID is a representation of the primary key value of the row and hence will vary in size depending on the object to which it points.

•\ JSON: New with Oracle 21c is the JSON datatype. You can now store JSON data natively in the database in a binary format.

Many types are apparently missing from the preceding list, such as INT, INTEGER, SMALLINT, FLOAT, REAL, and others. These types are actually implemented on top of one of the base types in the preceding list—that is, they are synonyms for the native Oracle type. Additionally, datatypes such as XMLType, SYS.ANYTYPE, and SDO_GEOMETRY are not listed because we will not cover them in this book. They are complex object types comprising a collection of attributes along with the methods (functions) that operate on those attributes. They are made up of the basic datatypes listed previously and are not truly datatypes in the conventional sense, but rather an implementation, a set of functionality, that you may make use of in your applications.

Now, let’s take a closer look at these basic datatypes.