Oracle Database Datatype

In this Oracle tutorial, we will learn how to use the datatype in oracle database 21c. Moreover, we will take a look at multiple tables where the Oracle data types, including character, NUMBER, DATE, binary (LOB, RAW, and LONG RAW), ROWID and UNROWID, and XML data types, will be briefly discussed.

Oracle Database Datatypes are utilized when describing the type of data that can be stored in a database table. If we need to store a string kind of data in a particular column of a table, for instance, we must specify a string data type for this column.

Oracle Database Datatype
Oracle Database Datatype

Oracle Database Datatype

  • Integer, floating-point, Boolean, and other types of data are all specified by their respective data types.
  • Multiple categories for user-defined types that can be used as data types are provided by Oracle Database in addition to a number of built-in data types.

Oracle Numeric data type

A numeric Datatype is one of the basic Oracle Database datatypes used to store numeric values. Here are details

TypesDescriptionSize
Number(p,s)Numbers with a p precision and s denote scale.Precision might be between 1 and 38.
The scale’s possible range is -84 to 127.
floatFloating-point numbers with binary precision are represented by the FLOAT datatype.This data type has a default precision of 38 decimals or 126 binary.
dec(p,s)Numbers with a p precision and s denote scale.Precision might be between 1 and 38.
decimal(p,s)Numbers with a p precision and s denote scale.Precision can range from 1 to 38.
RealIt is used to store single-precision floating-point numbers.floating-point number with a binary precision of 63, or 18 decimals.
SmallintIt is used to store small integersIts range value is from -32,768 to 32,767.
Oracle Numeric data type

Read: How to Check Oracle Database Version

Oracle character Datatypes

A fixed-length character string is what the CHAR data type specifies. Oracle returns an error if you insert a value that is too lengthy for the column or blank-pads it to the correct length if the value is shorter than the column length. For character data, the following data types are utilized.

TypesDescriptionRange in characters
char(size)Character data with a predetermined length is stored using the CHAR data type.2000 bytes
NCHARA data type that can only be used with Unicode is NCHAR. The column length in characters is specified when a table with an NCHAR column is created.Maximum column size
NVARCHAR2Although the character set will be the character set, this data type is identical to the VARCHAR2 data type.Maximum 4000 bytes
VARCHAR2A character string with a variable length is defined by the VARCHAR2 data type. The most bytes or characters a VARCHAR2 column can store are provided when the column is created.The VARCHAR2 data type is used to describe character strings with various lengths. A VARCHAR2 column’s maximum storage capacity in bytes or characters is specified when the column is formed.
Oracle character Datatypes

Read: How to create a user in Oracle

Date/Time Datatype

Date and time information is stored in the DATE data type (represented in both character and numeric data types). Oracle saves the year, month, day, hour, minute, and second for each DATE value.

DatatypeDescriptionRange
DateIt is used to provide the YYYY-MM-DD date format. It can handle values between “1000-01-01” and “9999-12-31.”Jan 1, 4712 BC, and Dec 31, 9999 AD.
DatetimeTo specify a date and time combination, use this. Its format is hh:mm:ss (YYYY-MM-DD). The range that is supported is from “1000-01-01 00:00:00” to “9999-12-31 23:59:59”.“1000-01-01 00:00:00” to “9999-12-31 23:59:59”.
TimeStampIt serves as a timestamp specification. The amount of time since the Unix epoch, or “1970-01-01 00:00:00 UTC,” is stored as its value. YYYY-MM-DD hh:mm:ss is the format.The range it supports is from “1970-01-01 00:00:01” UTC to “2038-01-09 03:14:07” UTC.
TimeIt’s utilized to define the time format. hh:mm:ss is the format used.Its supported range is from ‘-838:59:59’ to ‘838:59:59’
YEARIn four-digit format, it is used to identify a year.values between 1901 and 2155 in four-digit format, as well as 0000.
Interval
Date/Time Datatype

Read: How to check database status in Oracle

Large object Datatypes

A group of data types known as large objects (LOBs) is made to hold massive volumes of data.

Datatype ExplanationRange
blobA binary data file that is unstructured and has a maximum capacity of 128 TB is stored using the BLOB data type.It is an Oracle data type that can hold up to 4 GB of data.
clobstores character data in both single- and multi-byte formats.The maximum length of a CLOB (character large object) value is 2,147,483,647 characters.
nclobThe multibyte national character set is used to store character big objects in the NCBLOB data type.Up to 4 gigabytes of character data can be stored in NCLOB.
bfilefile locators that direct users to a server file system’s binary file (outside the database).The maximum file size supported is 4 gigabytes.
Large object Datatypes

Read: Install sample schemas 21c in Oracle

Rowid Datatypes

The permanent, unique identifiers for each row in the database are called ROWIDs. The following are the Rowid Datatypes in Oracle.

DatatypeDescriptionSize
RowidEach database record has a unique rowid or physical address. It has binary data with a fixed lengthThe 10-byte physical address of a row is effectively represented by a rowid.
urowidThe UROWID data type, where U stands for universal, contains both the logical and physical addresses (rowids) of the row in tables that are both structured by index.4000 bytes is the maximum size and the default value.
Rowid Datatypes

Read: How to create a table in Oracle

Spatial DataTypes

Users of geoimaging, geographic information systems (GIS), and location-enabled applications will find Oracle Spatial to make spatial data management simpler and more simple.

DatatypeExplanation
SDO_GEOMETRYAny table that contains a column of type SDO GEOMETRY needs an additional column, or combination of columns, that serves as the table’s exclusive primary key.
SDO_TOPO_GEOMETRYThis type represents a topology geometry that is stored in a user-defined table as an object type SDO TOPO GEOMETRY with a single row and a single column.
SDO_GEORASTEROne row is used to store a raster grid or picture object.
Spatial DataTypes

Read: How to backup a table in Oracle

Media DataTypes

Similar to Java or C++ classes, Oracle Multimedia uses object types to define multimedia data. Each of these object kinds has methods and characteristics, including media data and metadata.

DataTypeDescription
ORDAudioIt enables the control and storage of audio data.
ORDDicomDigital Imaging and Communications in Medicine (DICOM) data storage, management, and manipulation are supported by the Oracle Multimedia ORDDicom object type.
ORDDocThe management and storage of any media data, including image, audio, and video data, is supported by the ORDDoc object type.
ORDImageIt enables the organization and storage of image data.
ORDVideoVideo data management and storage are supported via the Oracle Multimedia ORDVideo object type.
ORDImageSignatureYour code should no longer include the ORDImageSignature object type because it has been deprecated.
Media DataTypes

Read: How to add a foreign key in Oracle

Xml Datatype

Due to the fact that XMLType is a system-defined type, you can use it as a function argument or the data type of a table or view column.

DatatypeExplanation
XMLIf you need to use object-relational storage to store structured data in the XMLTYPE column, you must first register an XML schema. Then, you must save XML data in an XMLTYPE that is based on an XML schema.
Xml Datatype

Read: Oracle Stored Procedure

Raw DataTypes

Byte strings and binary data are both stored in the RAW and LONG RAW data types. You can use them, for instance, for music files, pictures, and other media items.

DataTypeDescription
RawRaw binary data of length size bytes and the maximum size is 2000 bytes
LongRawThis is raw binary data of variable
Raw DataTypes

In this article, we have discussed what are the various Oracle Database Datatypes in version 21c.

Also, take a look at some advanced Oracle tutorials.