DATA TYPES
ORACLE

A Guide to Data Types in Oracle

intro

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 the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
ORACLE logo thetable ORACLE
THE ORACLE 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 NUMBERINTEGER, and FLOATNUMBER 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 CHARNCHARVARCHARVARCHAR2, and NVARCHARCLOBNCLOB, and LONGCHAR 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 TIMESTAMPDATE 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 CLOBBLOB is used to store binary data, while CLOB is used to store character data.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

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 TypeCategoryDescription
NUMBERNumericUsed to store numeric values with high precision
INTEGERsNumericUsed to store whole number
FLOATNumericUsed to store approximate numeric values
CHARCharacterUsed to store fixed-length character strings
NCHARCharacterUsed to store fixed-length national character set strings
VARCHAR2CharacterUsed to store variable-length character strings
NVARCHAR2CharacterUsed to store variable-length national character set strings
CLOBLarge ObjectUsed to store large character data
NCLOBLarge ObjectUsed to store large national character set data
LONGLarge ObjectUsed to store variable-length character data
BLOBLarge ObjectUsed to store binary data
DATEDate and TimeUsed to store date and time information
TIMESTAMPDate and TimeUsed to store more precise date and time information
RAWOtherUsed 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/

Download DbVisualizer.
Download DbVisualizer.

For the DbVisualizer setup, we need to install the JDK 17 first, so download JDK 17 from the official Oracle website. Make sure to download the package applicable to your operating system.

Download JDK 17.
Download JDK 17.

After downloading JDK, install it and verify to figure out the version of JDK using the following command in your CLI.

Verify JDK version.
Verify JDK version.
Copy
        
1 java -version

Now open the downloaded “.exe” file and install DbVisualizer.

Install DbVisualizer.
Install DbVisualizer.

Select the right directory for installation.

Select destination directory.
Select destination directory.

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.

Select file associations.
Select file associations.

Select if you want to create a desktop icon and press the “Next” button.

Select additional tasks.
Select additional tasks.

The installation will start - let it finish before performing any additional steps.

Installing.
Installing.

Now click on the “Finish” button and run the DbVisualizer.

Complete installation.
Complete installation.

DbVisualizer has been successfully installed.

Successfully installed DbVisualizer.
Successfully installed DbVisualizer.

2. Download and Install the Oracle Database

Go to this URL and download the Oracle database.

Download Oracle.
Download Oracle.

Now extract the downloaded .zip file.

Extract zip file.
Extract zip file.

Open the extracted folder and right click on the “setup.exe” and run it as an administrator.

Run installer as administrator.
Run installer as administrator.

Select the marked checkbox and click “Next.”

Setup Oracle database.
Setup single instance of Oracle database.

Now select the “Desktop Class” and press “Next”.

Install on desktop.
Select to install desktop version.

Choose the “Create New Windows User” option. Write the username and password of your choice and click “Next.”

Setup new windows user.
Setup new windows user.

Select the location for database installation, provide further credentials, and press “Next.”

Select installation location.
Select installation location.

Now save the response file for the future usage and import of your settings and click on “Install.”

Save response file.
Save response file.

Installation is in process now.

Oracle is installing.
Oracle is installing.

Once the installation has been successfully completed, you can verify it using the URL mentioned below.

Successful installation.
Successful installation.

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.

Verify installation with Oracle Enterprise Manager Database Express.
Verify installation with Oracle Enterprise Manager Database Express.

After logging in, you will see the Oracle dashboard.

Oracle dashboard.
The Oracle dashboard.

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.

Create a new connection with the Oracle database by using DbVisualizer.
Create a new connection to Oracle with DbVisualizer.

Now enter the username as “SYS” and Oracle DB password, choose the SYS Role as “SYSDBA”, and then click on the “Connect” button.

Provide credentials for the Oracle database.
Provide credentials for the Oracle database.

Now the connection has been established.

Connected to Oracle database.
Connected to Oracle database.

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.

Open a new SQL Commander in DbVisualizer.
Open a new SQL Commander in DbVisualizer.

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.
SQL query creating a products table.
SQL query creating a products table.
Copy
        
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.

SYS schema annotation.
SYS schema annotation.

The table has been created successfully.

Successfully created table.
Successfully created the Products table.

Data Insertion into Table Columns

Execute the following query to insert data into the table.

Query inserting data into the products table.
Query inserting data into the products table.
Copy
        
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.

Successfully inserted data.
Successfully inserted data.

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.

Dbvis download link img
About the author
Igor Bobriakov
Igor Bobriakov
Igor 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 or Twitter.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CASE Statement: Definitive Guide

author Antonello Zanini tags CASE ORACLE POSTGRESQL SQL SQL SERVER 5 min 2024-04-29
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗