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
- 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
| Types | Description | Size |
| 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. |
| float | Floating-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. |
| Real | It is used to store single-precision floating-point numbers. | floating-point number with a binary precision of 63, or 18 decimals. |
| Smallint | It is used to store small integers | Its range value is from -32,768 to 32,767. |
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.
| Types | Description | Range in characters |
| char(size) | Character data with a predetermined length is stored using the CHAR data type. | 2000 bytes |
| NCHAR | A 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 |
| NVARCHAR2 | Although the character set will be the character set, this data type is identical to the VARCHAR2 data type. | Maximum 4000 bytes |
| VARCHAR2 | A 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. |
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.
| Datatype | Description | Range |
| Date | It 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. |
| Datetime | To 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”. |
| TimeStamp | It 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. |
| Time | It’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’ |
| YEAR | In four-digit format, it is used to identify a year. | values between 1901 and 2155 in four-digit format, as well as 0000. |
| Interval |
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 | Explanation | Range |
| blob | A 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. |
| clob | stores 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. |
| nclob | The 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. |
| bfile | file locators that direct users to a server file system’s binary file (outside the database). | The maximum file size supported is 4 gigabytes. |
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.
| Datatype | Description | Size |
| Rowid | Each database record has a unique rowid or physical address. It has binary data with a fixed length | The 10-byte physical address of a row is effectively represented by a rowid. |
| urowid | The 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. |
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.
| Datatype | Explanation | |
| SDO_GEOMETRY | Any 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_GEOMETRY | This 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_GEORASTER | One row is used to store a raster grid or picture object. |
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.
| DataType | Description |
| ORDAudio | It enables the control and storage of audio data. |
| ORDDicom | Digital Imaging and Communications in Medicine (DICOM) data storage, management, and manipulation are supported by the Oracle Multimedia ORDDicom object type. |
| ORDDoc | The management and storage of any media data, including image, audio, and video data, is supported by the ORDDoc object type. |
| ORDImage | It enables the organization and storage of image data. |
| ORDVideo | Video data management and storage are supported via the Oracle Multimedia ORDVideo object type. |
| ORDImageSignature | Your code should no longer include the ORDImageSignature object type because it has been deprecated. |
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.
| Datatype | Explanation |
| XML | If 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. |
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.
| DataType | Description |
| Raw | Raw binary data of length size bytes and the maximum size is 2000 bytes |
| LongRaw | This is raw binary data of variable |
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.
- Connect to Oracle Database using Python
- How to Fetch Data from Oracle Database in Python
- Connect Excel to Oracle Database
- Oracle Interval_year datatype
- Oracle Long Datatype
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.