A Guide to Data Types in Oracle
Published: 2023-09-25 07:00:00

**This article aims to educate readers about the different types of data that can be stored in an Oracle database and how the database management tool DbVisualizer can be utilized to manage these data types. It will provide an overview of the various data types available in Oracle, including numerical, character, date and time, and large object database types.**

---

### Tools used in this tutorial
[DbVisualizer](https://www.dbvis.com/download/), top rated database management tool and SQL client
The [Oracle](https://www.oracle.com/se/database/technologies/oracle-database-software-downloads.html) Database

---
The article will then demonstrate the steps to connect to an Oracle database using DbVisualizer, create table structures with different data types and insert data. The objective of this article is to help readers comprehend the importance of data types in an Oracle database and how to use DbVisualizer to manage them effectively.

## Understanding Data Types in Oracle

A data type is a category that defines the type of values that a column can hold. In a database, a data type is a specification that determines the type of data that can be stored in a column of a table. This ensures that only the appropriate data type can be stored in the column.

### Why are Data Types Important?

Data types are critical in any database management system as they ensure the accuracy of the data stored in the database and efficiency working with that data. Specifying the type of data that can be stored in a column helps prevent errors, save space, and improve performance. Properly choosing a data type makes it easier to understand and manage the data in our database instance.

### How to Select the Correct Data Type?

Choosing the right data type involves considering factors such as the type of data, size of the data, performance requirements, portability needs, and potential future use of the data. By considering these factors, you can select a data type that best represents the type of data, helps optimize storage and retrieval performance, and accommodates any future requirements.

## Datatypes in Oracle

Oracle, like many relational databases, offers a wide range of datatypes to store different kinds of data. To ensure the accuracy and efficiency of your queries, it is important to have a good understanding of the different datatypes in Oracle and to select the most suitable one for each column. The datatypes offered by Oracle are as follows:

### 1. Numerical Datatypes
Oracle offers several numerical datatypes, including `NUMBER`, `INTEGER`, and `FLOAT`. `NUMBER` is used to store numeric values with high precision, `INTEGER` is used to store whole numbers, and `FLOAT` is used to store approximate numeric values.

### 2. Character Datatypes
Oracle has 8 character datatypes `CHAR`, `NCHAR`, `VARCHAR`, `VARCHAR2`, and `NVARCHAR`, `CLOB`, `NCLOB`, and `LONG`. `CHAR` and `VARCHAR2` are more important. `VARCHAR2` is used to store variable-length character strings, while `CHAR` is used to store fixed-length character strings.

### 3. Date and Time Datatypes
Oracle provides two datatypes for date and time information: `DATE` and `TIMESTAMP`. `DATE` is used to store date and time information, and `TIMESTAMP` is used to store more precise date and time information.

### 4. Large Object Datatypes
Oracle has two datatypes for large objects, `BLOB` and `CLOB`. `BLOB` is used to store binary data, while `CLOB` is used to store character data.

### 5. Raw Datatype
`RAW` is used to store variable-length binary data. It's often used for data that's not meant to be interpreted or manipulated, like encrypted information.

Overview of all oracle data types
The table below provides an overview of all the datatypes in Oracle.

| Data Type 	| Category      	| Description                                                  	|
|-----------	|---------------	|--------------------------------------------------------------	|
| NUMBER    	| Numeric       	| Used to store numeric values with high precision             	|
| INTEGERs  	| Numeric       	| Used to store whole number                                   	|
| FLOAT     	| Numeric       	| Used to store approximate numeric values                     	|
| CHAR      	| Character     	| Used to store fixed-length character strings                 	|
| NCHAR     	| Character     	| Used to store fixed-length national character set strings    	|
| VARCHAR2  	| Character     	| Used to store variable-length character strings              	|
| NVARCHAR2 	| Character     	| Used to store variable-length national character set strings 	|
| CLOB      	| Large Object  	| Used to store large character data                           	|
| NCLOB     	| Large Object  	| Used to store large national character set data              	|
| LONG      	| Large Object  	| Used to store variable-length character data                 	|
| BLOB      	| Large Object  	| Used to store binary data                                    	|
| DATE      	| Date and Time 	| Used to store date and time information                      	|
| TIMESTAMP 	| Date and Time 	| Used to store more precise date and time information         	|
| RAW       	| Other         	| Used to store variable-length binary data                    	|


## Connecting and Managing Oracle Data Types with DbVisualizer

DBVisualizer can help Oracle users manage and query data types by providing information about data types, allowing data type conversion and validation, and querying data by data type. It provides a user-friendly interface for managing and querying data in the database. To visualize the integration of DbVisualizer with Oracle, we have to follow a few steps discussed below.

### 1. Download and Install DbVisualizer

First go to the following website link and download DbVisualizer. https://www.dbvis.com/download/
<br />
![Download DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5a8wyivvggdc1kvvx8q8.png)
<figure><figcaption>Download DbVisualizer.</figcaption></figure>
<br />
For the DbVisualizer setup, we need to install the JDK 17 first, so [download JDK 17 from the official Oracle website](https://www.oracle.com/java/technologies/javase/jdk17-archive-downloads.html). Make sure to download the package applicable to your operating system.
<br />
![Download JDK 17.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2nlpn5knxpvxzj952zzb.png)
<figure><figcaption>Download JDK 17.</figcaption></figure>
<br />
After downloading JDK, install it and verify to figure out the version of JDK using the following command in your CLI.
<br />
![Verify JDK version.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/as3au817guf04gl6ckjc.png)
<figure><figcaption>Verify JDK version.</figcaption></figure>
<br />

```
$ java -version
```
Now open the downloaded “.exe” file and install DbVisualizer.
<br />
![Install DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dhiu3ipf26nj2n8pfeo4.png)
<figure><figcaption>Install DbVisualizer.</figcaption></figure>
<br />
Select the right directory for installation.
<br />
![Select destination directory.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pj9kmgim634svi1el2uo.png)
<figure><figcaption>Select destination directory.</figcaption></figure>
<br />
Check mark the DbVisualizer SQL file option if you want DbVisualizer to open all SQL files in the future, and click on the “Next” button.
<br />
![Select file associations.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vcepsthjfhhbwaelgmgl.png)
<figure><figcaption>Select file associations.</figcaption></figure>
<br />
Select if you want to create a desktop icon and press the “Next” button.
<br />
![Select additional tasks.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a97p0g37zujy43ujdnqr.png)
<figure><figcaption>Select additional tasks.</figcaption></figure>
<br />
The installation will start - let it finish before performing any additional steps.
<br />
![Installing.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5cm9tjuqae9taqepqyt3.png)
<figure><figcaption>Installing.</figcaption></figure>
<br />
Now click on the “Finish” button and run the DbVisualizer.
<br />
![Complete installation.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oarxwuedtfvpud7bfh11.png)
<figure><figcaption>Complete installation.</figcaption></figure>
<br />
DbVisualizer has been successfully installed.
<br />
![Successfully installed DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0vwqs5c7xdqe64nc39td.png)
<figure><figcaption>Successfully installed DbVisualizer.</figcaption></figure>
<br />

### 2. Download and Install the Oracle Database
Go to [this URL](https://www.oracle.com/ae/database/technologies/oracle19c-windows-downloads.html) and download the Oracle database.
<br />
![Download Oracle.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qrheyllg9ly3u4j7nw26.png)
<figure><figcaption>Download Oracle.</figcaption></figure>
<br />
Now extract the downloaded .zip file.
<br />
![Extract zip file.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3fcm5pte45kg6g850z7i.png)
<figure><figcaption>Extract zip file.</figcaption></figure>
<br />
Open the extracted folder and right click on the “setup.exe” and run it as an administrator.
<br />
![Run installer as administrator.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/78zt9105y6ucnfbrdhqy.png)
<figure><figcaption>Run installer as administrator.</figcaption></figure>
<br />
Select the marked checkbox and click “Next.”
<br />
![Setup single instance of Oracle database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2uv78sywa1bivjmdclkz.png)
<figure><figcaption>Setup single instance of Oracle database.</figcaption></figure>
<br />
Now select the “Desktop Class” and press “Next”.
<br />
![Select to install desktop version.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v53ol1nmoxd4g7e2p6pp.png)
<figure><figcaption>Select to install desktop version.</figcaption></figure>
<br />
Choose the “Create New Windows User” option. Write the username and password of your choice and click “Next.”
<br />
![Setup new windows user.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ygrnhci9so7owoyho3ff.png)
<figure><figcaption>Setup new windows user.</figcaption></figure>
<br />
Select the location for database installation, provide further credentials, and press “Next.”
<br />
![Select installation location.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vwd5tedzmt6852f7jrw0.png)
<figure><figcaption>Select installation location.</figcaption></figure>
<br />
Now save the response file for the future usage and import of your settings and click on “Install.”
<br />
![Save response file.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4we4b14enxfvxbaqimb9.png)
<figure><figcaption>Save response file.</figcaption></figure>
<br />
Installation is in process now.
<br />
![Oracle is installing.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvh5n5a6aladzaf8mrmz.png)
<figure><figcaption>Oracle is installing.</figcaption></figure>
<br />
Once the installation has been successfully completed, you can verify it using the URL mentioned below.
<br />
![Successful installation.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b8i19d1brp5clf5no2mc.png)
<figure><figcaption>Successful installation.</figcaption></figure>
<br />
Once you reach the URL, you will see this screen. Provide the Username as “sys” and the password the same as what you have set for Oracle DB, and press the “Login” button.
<br />
![Verify installation with Oracle Enterprise Manager Database Express.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ys4ulla4s92c28jub4v9.png)
<figure><figcaption>Verify installation with Oracle Enterprise Manager Database Express.</figcaption></figure>
<br />
After logging in, you will see the Oracle dashboard.
<br />
![The Oracle dashboard.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/swhvi93irsgmysm9dnbq.png)
<figure><figcaption>The Oracle dashboard.</figcaption></figure>
<br />

### 3. Connecting to Oracle through DbVisualizer
Now we will create a new connection with the Oracle database by using DbVisualizer. Click on the highlighted plus icon and select the “Oracle 9i” option.
<br />
![Create a new connection to Oracle with DbVisualizer.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a9ttpwh8plqapciax5ut.png)
<figure><figcaption>Create a new connection to Oracle with DbVisualizer.</figcaption></figure>
<br />
Now enter the username as “SYS” and Oracle DB password, choose the SYS Role as “SYSDBA”, and then click on the “Connect” button.
<br />
![Provide credentials for the Oracle database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qk9weax9zto9j6sqzsnp.png)
<figure><figcaption>Provide credentials for the Oracle database.</figcaption></figure>
<br />
Now the connection has been established.
<br />
![Connected to the Oracle database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1htohg0vou1ljka5ou24.png)
<figure><figcaption>Connected to the Oracle database.</figcaption></figure>
<br />

### 4. Working with Oracle Using DbVisualizer
Now we will create a table with multiple data types, insert data into it, and visualize everything with DbVisualizer.

#### Create Table and Add Columns

Click on the plus icon to open a new SQL command tab.
<br />
![Open a new SQL Commander in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m5b9e50hnwjybbiufcw8.png)
<figure><figcaption>Open a new SQL Commander in DbVisualizer.</figcaption></figure>
<br />
Now write a SQL query in which a “products” table will be created with multiple columns.

- “productId” should have an `INTEGER` data type and should be set as the primary key, meaning it is a unique identifier for each row in the table.
- “productName” should be a `VARCHAR2(50)` data type and should be set to `NOT NULL`, meaning it cannot have a NULL value.
- “description” should have a `VARCHAR2(2000)` data type - let it have a NULL value too.
- Create a “price” column with a `NUMBER(10, 2)` data type and set it to NOT NULL, meaning it cannot have a NULL value. Also create a NUMBER datatype, with a precision of 10 and a scale of 2 - it will be used to store values with up to 10 digits with 2 digits after the decimal point.
- “quantity” with an `INTEGER` data type set to NOT NULL, meaning it cannot have a NULL value.
- “image” with a `BLOB` (binary large object) data type – make it able to have a NULL value. This data type will be used to store binary data such as images.
<br />
![SQL query creating a products table.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vh7bay0dnnujtrzknh8i.png)
<figure><figcaption>SQL query creating a products table.</figcaption></figure>
<br />

```
1 CREATE TABLE products (
2    productId INTEGER PRIMARY KEY,
3    productName VARCHAR2(50) NOT NULL,
4    description VARCHAR2(2000),
5    price NUMBER(10, 2) NOT NULL,
6    quantity INTEGER NOT NULL,
7    image BLOB
8 );
```
Now verify that the table has been created by looking into the “SYS” schema.
<br />
![SYS schema annotation.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bxka9nqqg1axys5v120z.png)
<figure><figcaption>SYS schema annotation.</figcaption></figure>
<br />
The table has been created successfully.
<br />
![Successfully created the Products table.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/29nl4j07jkjwh8d8jrh5.png)
<figure><figcaption>Successfully created the Products table.</figcaption></figure>
<br />

#### Data Insertion into Table Columns

Execute the following query to insert data into the table.
<br />
![Query inserting data into the products table.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcf7ggfnu63b8eysjdxi.png)
<figure><figcaption>Query inserting data into the products table.</figcaption></figure>
<br />
Query inserting data into the products table.

```
1 INSERT INTO products (productId, productName, description, price, quantity, image)
2 VALUES (1, 'Orange', 'Orange is sour and sweet fruit', 9.99, 10, NULL);
```
Data has been successfully inserted into the “products” table.



<br />
![Successfully inserted data.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yrxt7hwi52hjtjvpagea.png)
<figure><figcaption>Successfully inserted data.</figcaption></figure>
<br />

## Conclusion
In conclusion, selecting the appropriate Oracle data types for each column is crucial for maintaining database performance. Using a database management tool such as DbVisualizer can simplify the process of working with datatypes in Oracle.

With its user-friendly interface, DbVisualizer allows for easy connections to an Oracle database, the creation of tables with different datatypes, insertion and retrieval of data, and can help you improve database security. By utilizing DbVisualizer, database administrators and developers can effectively manage their Oracle databases and ensure that data is stored and processed correctly.

Having a good understanding of datatypes in Oracle and utilizing DbVisualizer to simplify database management tasks can greatly enhance the performance and reliability of an Oracle database.

To learn more about database management and news from the database world, make sure to keep an eye out on our TheTable blog, and until next time.

## About the author
Igor Bobriakov is an entrepreneur and educator with a background in marketing and data science. His passion is to help small businesses and startups to utilize modern technology for growth and success. Igor is Data Scientist and ML Engineer. He founded Data Science School in 2016 and wrote numerous award-winning articles covering advanced tech and marketing topics. You can connect with him on [LinkedIn](https://www.linkedin.com/in/bobriakov/) or [Twitter](https://twitter.com/ibobriakov).