December 14, 2022 By Lisandro Fernigrini

If you want to connect to an Oracle database from your computer, you will need a client tool to be able to interact with the database. There are many options available, we can classify them in several ways:

  • If they are graphical tools or command line tools (User Interface).
  • If they are designed only for Oracle databases or designed to connect to many database engines (relying on JDBC or ODBC connectors).
  • If they are provided by Oracle or designed by third parties.

Note: “Oracle Database Client” is an Oracle product that includes software libraries to connect to an Oracle database, and optionally some additional tools like SQL*Plus. There is also a “thinner” (smaller and simpler to install) version of the Database Client called Instant Client. On this article we are not analyzing Oracle Database Client or Oracle Instant Client. Rather, we are going to review the most relevant tools that allow users to interact with an Oracle database. Some of them rely on Oracle Client or Instant Client to connect to Oracle while others may simply use an Oracle JDBC or ODBC connector.

Multi Database Client Tools

Tools in this category allow users to interact with Oracle databases and databases from other providers (SQL Server, DB2, MySQL, Informix, Sybase, PostgreSQL, etc. etc.). They may not be as complete and powerful as those tools designed for a specific database engine, but they provide a good set of functionalities with the same IDE and look & feel. They are preferred by professionals that work in environments with different databases providers since they provide a unified way to interact with all of them.

Beekeeper Studio

Beekeeper Studio is a modern, multi-platform SQL Editor and Database Manager that supports most used database engines (like Oracle and SQL Server) both on-premises and cloud environments (Redshift). Obviously we’re biased, but we think Beekeeper is the best tool for most software engineers who are using Oracle to build a website or app.

Most relevant features are:

  • SSL and tunneled SSH connections allow connections through firewalls.
  • SQL Auto Completion and Saved Queries reduces the time required to write commonly used SQL sentences.
  • Table Data editor to quickly view and modify data, staging changes and applying all of them together once the “changeset” is complete.
  • Table / Query Export feature supports exporting data in CSV, Excel, JSON, Markdown and spreadsheet-friendly formats

Download from the Beekeeper Studio download page.

Pros

  • Clean and intuitive UI makes it very simple to use.
  • Fully functional, free (open source) Community Edition plus a paid Ultimate Edition that includes team shared Cloud Storage for connections & queries.
  • Runs on Windows, Linux and MacOS.

Cons

  • Does not include advanced administration or performance options.

DbVisualizer

DbVisualizer is a powerful multi-platform IDE that allows you to connect to any data sources that supports a JDBC connection.

Most relevant features are:

  • Advanced SQL Editor with auto-complete and auto-formatting options.
  • Inline data editor to modify and stage changes and then commit all of them.
  • Basic performance analysis including Explain Plan monitor.
  • Database scheduling using native features available in Oracle, SQL Server, PostgreSQL, MariaDB and MySQL.

DbVisualizer 21-day non-commercial trial can be downloaded from here.

Pros

  • Support +50 different sources. Includes some JDBC drivers but you can add and update them as required.
  • Runs on Windows, Linux and MacOS.

Cons

  • Relatively expensive, there is a free edition but with a very limited set of features (like being able to have only one SQL tab opened, that is simply annoying).
  • Outdated UI

DBeaver

This free multi-platform tool provides access to many database engines including Oracle. The most important features are:

  • Data Editor allows to search, filter, and edit data on a very simple and intuitive way.
  • SQL Editor with templates, auto complete and SQL Formatting.
  • ER Diagram to graphically visualize the database model.

Besides the free Community edition, there is a DBeaver Pro product with three editions (Lite, Enterprise and Ultimate) that includes several advanced features like support to NoSQL database engines, editable ER Diagrams, Cloud Support, etc. A free trial is available upon request.

Pros

  • Support +80 different sources. Any source with a JDBC drivers can be used.
  • Runs on Windows, Linux and MacOS.
  • Free version is suitable for basic database development and paid editions are affordable.

Cons

  • Does not include database administration nor performance options on the free edition.
  • Confusing and outdated UI.
  • It has a “kitchen-sink” approach to features.

SQuirreL SQL

SQuirreL is a free graphical tool developed in Java that can connect to any JDBC compliant database. Originally designed more than 20 years ago, it is still actively maintained. Main features include:

  • Basic navigation panel.
  • SQL Editor with code completion feature.
  • In Line data editor.
  • Graphical ERD tool with Query Builder.
  • Plugin architecture that allows to add functionalities for specific vendors.

SQuirreL is open source, free to use, and can be downloaded from its sourceforge repository.

Pros

  • Open source, multi-platform and free to use.
  • Supports 30+ database and the Plugin Architecture allows to add vendor specific features.

Cons

  • Relatively old design, not very user friendly.
  • Installation and configuration is not simple.

DataGrip

This IDE developed by Jetbrains allows to connect to more than 40 different data sources using JDBC connectors. It is a modern tool that includes:

  • Query console with context sensitive auto-complete, local history and code formatter.
  • Version control integration with GIT, SVN, Mercurial.
  • Schema Difference tool allows to easily sync databases.
  • In-Line data editor, Import/Export and Data Compare tools.

DataGrip can be licensed for organizations or individual use and is also free to use for students and non-commercial open-source projects. You can also get a 30-day trial on DataGrip site.

Pros

  • Multi-platform (Windows, MacOS and Linux) and with some free options available.
  • Very complete set of features both for database designers and developers.

Cons

  • Subscription licensing model
  • Lacks database administration features.

Oracle Specific Database Client Tools

This kind of tools are designed specifically to deal with Oracle databases, so they provide more advanced options that those meant to be generic. They are preferred by professionals that work purely with Oracle and want to be able to perform all kind of tasks in a single tool.

SQL*Plus

SQL*Plus is a command line tool developed by Oracle, included on every Oracle Database and Oracle Client installation software (so it is available if you install either Oracle Database or Oracle Client) that allows to execute SQL scripts, SQL sentences and PL/SQL blocks against an Oracle database.

It has very basic editing and formatting options and some very limited error handling using the WHENEVER command. Nowadays is mostly used to execute batch scripts that do not required human interaction.

Pros

  • Free to use, included on Oracle Database and Oracle Database Client software installation.

Cons

  • Not very user friendly.
  • Is currently not being updated with new features (see SQLcl below).

SQLcl

This is a free to use, multi-platform command line interface (name comes from SQLcommand line) for Oracle database, using the same engine as SQL Developer (see details below) but without the graphical interface. It is being actively developed by Oracle and intends to replace SQL*Plus. SQLcl includes a lot of user-friendly enhancements like statement completion and command recall while being 100% compatible with SQL*Plus existing scripts, and it also includes Liquibase integration.

SQLcl does not require installation of Oracle Client since uses an included Oracle JDBC driver to establish connections, but you can force a connection to be established using an already installed Oracle Client (known as “thick” connection).

SQLcl can be downloaded from Oracle download page for free.

Pros

  • Free to use.
  • Easy to install and configure.
  • Developed by Oracle, frequently updated.

Cons

  • Just a command line tool, so not suited for all users.

SQL Developer

SQL Developer is a free to use, multi-platform Integrated Development Environment for Oracle databases developed by Oracle. Uses the same engine as SQLcl but with a powerful graphical interface developed in Java and available on Windows, Linux, and OS X.

Most relevant features are:

  • PL/SQL Development IDE allows to compile, debug, document, test, and handle source control of PL/SQL code.
  • Database Administration and Monitoring options allows for common task like handling security and storage.
  • Data Modeling tool (SQL Developer Data Modeler).
  • Migration Assistant to migrate from 3rd party database engines to Oracle.

It can be used to connect Oracle databases version 11gR2 and newer, both on-premises or in Oracle Cloud (OCI). SQL Developer does not require additional Oracle Client software to be installed, just download from Oracle download page and unzip and you can start using it.

Pros

  • Free to use.
  • Easy to install and configure.
  • Developed by Oracle, frequently updated.

Cons

  • May be slow to load and consumes more resources (memory).

PL/SQL Developer

PL/SQL Developer is an IDE for Oracle designed for users that need to develop program units in an Oracle Database using PL/SQL language. It is supported only on Windows.

PL/SQL Developer most relevant features are:

  • PL/SQL Development IDE including syntax highlighting, PL/SQL Beautifier, object description, etc. etc.
  • Integrated debugger and profiler allow to test, debug, and verify performance of developed code.
  • Integration with Scheduler option in Oracle Database.
  • Integrated Database Diagrams allow to easily visualize portions of complex databases.
  • Command Window emulates SQL*Plus to develop and test scripts that can be later executed on SQL*Plus or SQLcl.
  • Many plugins are available to sum additional features like Documentation, Unit Testing, and Version Control.

PL/SQL Developer has a 30-day free trial available on Allround Automations download page.

Pros

  • Probably the most complete PL/SQL Editor with debugger, profiler, and beautifier.

Cons

  • Supported only on Windows platforms.
  • Requires installation of Oracle Database Client software.
  • Includes only some basic database administration features.

TOAD for Oracle

This software developer by Quest was a must-have piece for all Oracle database administrators some years ago. It is a graphical application that was originally oriented for database administration, but currently also offers a simpler (and cheaper) version for developers.

TOAD for Oracle most relevant common features are:

  • SQL and PL/SQL Editor with Debugger and Query Builder.
  • SQL Optimizer to detect missing indexes.
  • Schema & Data Compare tools.
  • Data Generator.

Additionally, the TOAD for Oracle DBA Edition offers:

  • Data Modeler.
  • Performance diagnostics and predictive trending.
  • Support for Oracle RAC and Oracle on Exadata.

TOAD for Oracle Developer Edition offers Advanced PL/SQL Unit Testing besides the common utPLSQL unit testing feature.

There are 30-day trial versions of TOAD for Oracle DBA Edition and Developer Edition.

Pros

  • Probably the most complete suite of database administration tools for Oracle, including database health and security monitoring features.

Cons

  • Expensive.
  • Requires installation of Oracle Database Client software (either full client or Instant Client).

dbForge Studio for Oracle

This tool from Devart includes several modules with very powerful features like:

  • PL/SQL Development module with Intellisense, Query Formatter, Query Profiler & PL/SQL Debugger.
  • Visual Database Designer with table editor and query builder.
  • Change Management module including Data import and export, data comparison / synchronization and schema comparison / synchronization.

dbForge studio has three different paid editions (Standard / Professional / Enterprise) and a free Express one. You can download a free, 30-day trial that automatically switches to the express set of features after the trial period ends.

Pros

  • Very complete set of features both to design databases and to implement them.

Cons

  • All three paid editions are relatively expensive, while the free edition is very limited.
  • Available only on Windows platform (both 64 and 32 bit).

Mobile Client Tools

Besides the different desktop client tools that we have analyzed, there are some options to connect to an Oracle database from a mobile device, although not as many as with other database engines like MySQL, SQLite and PostgreSQL.

Android Client Tools

The following tools work on Android devices:

iOS Client Tools

The following was also supported on Android but nowadays works in iOS

Plugins and Tools for Development Clients

If you are a developer that already work with an IDE for your preferred development language, then there are multiple options to integrate Oracle connectivity and features into your preferred IDE. Here is a list of a few of them that are developed by Oracle and are completely free to use:

Oracle Developer Tools for Visual Studio

This add-on, compatible with Visual Studio 2017 and 2019, allows fully integration between Microsoft development IDE and Oracle databases, and is available for download on Oracle ODT site. Additional resources can be found at the .Net and Visual Studio download page.

Oracle PHP Extension

Both the PHP OCI8 extension and the PDO_OCI driver are tools developed and maintained by Oracle that are available as part of PHP open source project.

Node.js node-oracledb

This add-on supports both basic and advanced Oracle features and is available for download on Oracle GitHub repository.

Python python-oracledb Driver

This driver is an extension module that allows Python programs to connect to Oracle databases and is available on Oracle GitHub repository.

Make Oracle Fun Again With Beekeeper Studio

Write SQL, create tables, edit data, and have fun doing it! Beekeeper Studio is available for MacOS, Linux, and Windows.