In the article, we are going to provide a brief overview of the Oracle data types, namely character, NUMBER, DATE, binary (LOB, RAW and LONG RAW), ROWID and UNROWID, and XML data types. We will use dbForge Studio for Oracle to illustrate the examples with Oracle data types.
A data type defines the following:
- What values can be assigned for the specified type.
- How they can be stored.
- What properties can be associated with the value.
- What operations can be performed on the value.
In Oracle, each value has a data type that identifies how the data will be stored and processed by Oracle. So, when you create a table, you need to assign a data type for each column.
The following diagram outlines the data types supported by Oracle.
We will briefly examine the following data types:
Character datatypes
In Oracle, character data types store strings of letters, symbols, and numbers specified in the fixed-length field. It means that when you add a shorter value to the column than the column length, Oracle pads the string with spaces. When you add a longer value than the column length, the error occurs.
The character data types include the following:
- CHAR
- VARCHAR2
- VARCHAR
- NCHAR
- NVARCHAR2
The size of character data types can be specified either in bytes or characters.
The table displays a short overview of each character data type.
| Data Type | Description | Size | Syntax |
| CHAR | Stores a fixed-length string value. | From 1 to 2000 bytes | The maximum string length is defined either in bytes or characters. If you do not specify any parameter, BYTE will be set by default.
|
| NCHAR | Stores a fixed-length Unicode character string value. | From 1 to 2000 bytes | The maximum string length size is defined in characters.NCHAR(length) |
| NVARCHAR2 | Stores a variable-length Unicode character string value. | From 1 to 4000 bytes | The maximum string length size is defined in characters. Note: The number of characters specified refers to the maximum number of characters that the column can store. NVARCHAR2(length) |
| VARCHAR2 | Stores a variable-length string value. | From 1 to 4000 bytes | The maximum string length is defined either in bytes or characters. If you do not specify any parameter, BYTE will be set by default. Note: If the MAX_STRING_SIZE is STANDARD, the maximum size is 4000 bytes. If the MAX_STRING_SIZE is EXTENDED, the maximum size is 32767.
|
Note that the VARCHAR data type should not be used because its semantics might be modified. Instead, you can use VARCHAR2.
Numeric datatypes
Oracle supports the NUMBER data type that can store fixed or floating numeric values. The parameters of the data type define precision, which is the number of digits from 1 to 38, and scale, which is the number of digits after the decimal that ranges from -84 to 127.
Floating numeric values can be specified with the FLOAT number data type. In this case, precision is an optional parameter, and scale is not specified.
For example, let’s create a table to illustrate how the Oracle NUMBER data type works with the help of dbForge Studio for Oracle. For the numeric values, we specify precision and scale as 5 and 2 respectively. The scale rounds the value up to 2 digits after the decimal point.
CREATE TABLE NUMBER_TABLE (
VALUE_ONE NUMERIC(5, 2)
);In SQL Editor, integrated into dbForge Studio for Oracle, we insert the following values in the table:
To move on, in the output, you can see that the first value (101.99) was inserted as is in the statement, while the second and third values (88.771 and 56.753) were rounded up to the specified scale.
As for the fourth value (77777.77), the error message was displayed because the value is larger than the specified precision.
It should be mentioned that working in SQL Editor improves your code and increases efficiency due to its cutting-edge features, such as statement highlighting, context-sensitive code completion, SQL formatting, quick information about database objects, parameters and variables, etc.
DATE data type
The DATE data type stores date and time values in a table column. It includes a year, a month, a day, hours, minutes, and seconds. To illustrate this data type, we retrieve the current date to which the DATE data type has been declared in the Customers table.
LOB data types
The LOB data type is a binary string that can mainly store unstructured large objects such as images, photos, files, etc. The LOBs are divided into the following groups:
- Internal: They include the BLOB, CLOB, and NCLOB data types, which can be stored in the database.
- External; They include the BFILE data type. It is a read-only unstructured binary file that can be stored outside the database as a server-side operating file.
The BLOB data type stores an unstructured binary data file with a maximum size of up to 128 TB. The CLOB and NCLOB data types store data in a character set and cannot be used to store unstructured data, such as images, photos, etc. The maximum storage size is 128 TB.
RAW and LONG RAW data types
The RAW and LONG RAW data types store data in a binary format and byte strings. For example, you can use them for media objects, images, graphics, audio files, etc. In Oracle PL/SQL, the maximum storage size of the RAW and LONG RAW data types is 32767 bytes and 2 GB respectively.
In addition, data with the LONG RAW data type cannot be indexed, while RAW data type can be indexed.
ROWID and UROWID data types
The ROWID data types store the physical address of the row in a table, such as clustered and partitioned tables, index partitions and subpartitions.
The UROWID data type, in which U stands for universal, stores both physical and logical addresses (rowids) of the row in indexed-organized and foreign tables. The maximum size of the UROWID and ROWID data types is 3950 bytes and 10 bytes respectively.
For example, to see the row identifier (ROWID) of the customer whose CUSTOMERID equals 5 in the Customers table, execute the following SELECT statement:
XML data type
In Oracle, XML data can be stored in an XMLTYPE column or table. The XMLTYPE data type can be used to access, create, retrieve, or index XML data in the Oracle database. XML data in the XMLTYPE column can be stored either as a CLOB object or structured data.
When assigning XMLTYPE as a CLOB data type, Oracle stores the original XML file including whitespace in the CLOB column. This XMLTYPE column will inherit all restrictions of the CLOB column. This method should be used if you are not going to modify the XML document and want to save the entire document.
If you want to store structured data in the XMLTYPE column, first, you need to register an XML schema and then store XML data in an XML-schema-based XMLTYPE using object-relational storage.
Conclusion
In the article, we have reviewed Oracle data types, such as character, NUMBER, DATE, binary (LOB, RAW and LONG RAW), ROWID and UNROWID, and XML.
Considering the variety of Oracle data types, it would be more convenient and simpler to use a single tool that supports all these types. Of course, the market offers plenty of similar tools. But we are sure that you should use dbForge Studio for Oracle and explain why.
dbForge Studio for Oracle is one of the best IDEs available on the market. This Oracle IDE provides a lot of tools and functionalities that will help you increase developer productivity, improve the process of writing PL/SQL code, as well as make development, testing, analysis, and administration efficient and fast. For example, the Column Picker and Quick information about schema objects features allow you to select and insert relevant tables and columns available on the suggestion list, as well as view up-to-date information about the database object in SQL Editor.
Still in doubt? Well, then download a 30-day free trial version of dbForge Studio for Oracle and evaluate all the versatile features that the tool provides.
