Oracle SQLPlus Export To CSV Data: A Comprehensive Guide

Introduction

Oracle is a database that is widely used in the IT industry due to its extensive data management capabilities. In the era of big data, there is a surging demand for analytics. There are now, several tools to perform ETL on the data so that it can be analyzed and reports can be generated. Some of these tools need extracted data whereas some can directly connect to the data and extract it. The data which is extracted in CSV (Comma Separated Value) has gained popularity in recent times.

In this article, we will demonstrate to you, how to perform Oracle SQLPlus export to CSV data. We will also show you a simpler alternative, Hevo Data, a No-code platform that can perform ETL much faster.

However, before getting into Oracle SQLPlus export to CSV data, let us first understand Oracle and its features in brief.

What is Oracle?

Oracle has introduced a database known as Oracle DB, based on Relational Database Management System, handling structured data. It is one of the most widely used databases in the industry.

The data can be accessed via Structured Query Language (SQL), which has full data analytical function capabilities. Oracle DB is based on Relational Database Architecture that has extensive scaling ability and is majorly used by global enterprises.

Features Of Oracle

Let’s discuss the essential features of Oracle DB:

• Oracle DB has the capability of handling vast amounts of data.
• It has one of the best failover and recovery management systems for data.
• Oracle DB can be easily scaled up, provided the hardware.
• One can easily export the entire architecture/schema from one OS to another. 
• It provides cursor support, which enables the user to connect to Oracle through different programming languages.
• Oracle has consistent control over data integrity, due to which it guarantees the safe delivery of data.

What is Oracle SQL Plus?

SQL Plus is the most basic but interactive Oracle Database utility. It comes with a basic command-line user interface commonly used by users, administrators, and programmers. This batch query tool is installed with every Oracle Database Server or Client installation. It also supports a Windows Graphical User Interface (GUI) and the iSQL*Plus browser-based User Interface.

SQL*Plus has its own set of commands and environment, and it enables you to perform SQL, PL/SQL, SQL*Plus, and operating system queries. You can also generate reports interactively and output the results to a file with SQL*Plus. Its browser-based version, iSQL*Plus, allows you to dynamically generate reports with its dynamic reporting capability to run a script from a web page.

Launching SQL*Plus

Depending on your Oracle installation, you can launch SQL*Plus in many different versions or modes.

• SQL*Plus Command-line
• iSQL*Plus
• SQL*Plus for Windows

SQL*Plus Command-line

You can easily launch SQL*Plus from SQL*Plus Command-line by executing the sqlplus command.

$ sqlplus

This will connect you to the default database and you’ll need to enter your credentials to authenticate yourself.

If you need to connect to a different database, you can execute the below-mentioned command with your respective values.

$ sqlplus schema@//machine.domain:port/database

iSQL*Plus

You can also launch SQL*Plus from a browser-based version of its command-line utility, called iSQL*Plus.

You can do so by simply visiting iSQL*Plus URL: http://machine_name.domain:port/isqlplus

SQL*Plus for Windows

There also exists a Windows GUI version of SQL*Plus, which can typically be launched from your start menu on Windows: Start > Programs > Oracle > Application Development > SQL Plus.

Pre-requisites

For Oracle SQLPlus export to CSV, you’ll need to have:

• Oracle DB installed on-premise or on cloud instances.
• Command-line tool to access Oracle database.

Refer to the links below if you wish to install Oracle on Windows/Linux.

• Oracle Windows Install
• Oracle Linux Install

Exporting CSV Data From Oracle

Before you start, do take a look at the schema:

• The schema defined is HR.
• The table’s name is Employee.
• The ‘Employee’ table contains the following attributes:
  • “employee_id” – Primary key, which represents the ID of an employee.
  • “first_name” – First name of the employee.
  • “last_name” – Last name of the employee.
  • “email” – Email address of the employee.
  • “department_id” – Foreign key for department schema.

Oracle SQLPlus Export To CSV Using SPOOL Command

SQLPlus is an interface to query the data present in Oracle DB. It is available in the form of a command-line tool and a Windows-based GUI tool. The SPOOL command will be used to perform Oracle SQLPlus export to CSV data. Before beginning the steps to use the SPOOL command, there are a few configurations that need to be set.

Set The Configurations

You can change these settings at the runtime or by setting them as default in the User Profile in the login.sql file, which comes with the installation.

The variables are:

set colsep ,
set headsep off
set pagesize 0
set trimspool on

Let’s briefly discuss the usage of each of the above mentioned attributes:

• colsep is the column separator (delimiter) used to separate the fields in the CSV file. For this example, a comma is used. However, different separators can be used as well.
• headsep is required when the user wants to publish a header in the output file. In this example, this is set to off as a header is not needed.
• pagesize is the parameter used to control the number of lines per page in the output. Since you are writing to a file, set this number to 0.
• trimspool is set to on to remove trailing whitespace.

Run The Query

Once you set the required configuration, you need to enable the SPOOL command to spool the output to the CSV file. The syntax is:

spool file_path

The query to obtain the result set will be something like:

SELECT
   employee_id,
   first_name,
   last_name,
   email,
   department_id
FROM HR.EMPLOYEES;

You need to set the SPOOL command to off after the execution. The syntax is:

spool off

The combination of all the settings and queries will look like this:

set colsep ,
set headsep off
set pagesize 0
set trimspool on

spool c:/temp/oracle/output/employee.csv

SELECT
   employee_id,
   first_name,
   last_name,
   email,
   department_id
FROM HR.EMPLOYEES;

spool off

The Oracle SQLPlus export to CSV is finally accomplished.

An Alternative: Using Hevo Data

The previous method is one way of Oracle SQLPlus export to CSV. But not everyone would be interested in going through the messy and convoluted process of working with custom scripts. You might be interested in getting your CSV files out of Oracle painlessly into a destination and moving on with your business. There is a very robust and intuitive solution to accomplish the same.

Hevo Data is a modern ETL tool with extensive capabilities to extract data from a source (Oracle DB in this case) to a destination platform or a data warehouse.

Hevo Data, a No-code Data Pipeline, is a fully managed data integration solution that helps users migrate data from multiple sources like databases and cloud applications to their data warehouses.

Steps To Export Oracle Database Using Hevo Data

• Using the Hevo Data platform, connect to the Oracle database using connectors (Hevo has an extensive library of connectors) and configure it as a source by providing the URL, username, password, and other credentials.
• Configure the destination data warehouse (such as Redshift, Snowflake, etc.) and start moving data instantly!

Outputting a Query to a File

Now that you’re connected to SQL*Plus, you can begin creating your file. To do so, follow the below-mentioned steps.

• Modify SQL*Plus Configuration
• SPOOL Command
• Insert the Query

Modify SQL*Plus Configuration

This step is required to configure some SQL*PLus system settings using the SET statement.

You can manually alter these settings before file generation. However, depending on your needs, you can also change the defaults of various settings in your User Profile, located in the login.sql file.

Here is a list of settings.

set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize #
set numwidth #

There’s no need to change the first few settings, however, the final two settings need to be changed depending on your query.

• For linesize, the # value should be the total number of output columns you require in your resulting query file.
• numwidth is the column width used while outputting numeric values.

SPOOL Command

Now that you have configured the settings, it’s now time to instruct SQL*Plus to output your file. You can do so by using the SPOOL statement.

SQL*PLus will store the output of any query to the specified file when SPOOL is active.

spool file_path

After inserting your query, you also need to halt spool in order to close the output file. This can be done by using the spool off command.

spool off

Insert the Query

You can now finally insert your query. For the purpose of this demonstration, we’re outputting all employees from anHR.EMPLOYEES table.

SELECT
   employee_id,
   first_name,
   last_name,
   email,
   department_id
FROM HR.EMPLOYEES;

spool off

After the query statement, you can enter the spool off command.

That’s it, you’ve successfully generated a new text file using SQL*Plus.

Tip: Using a Script File

Instead of manually entering every line, you can also enter all the settings into a new script file. You can then execute this file in SQL*Plus with a single command.

Create a new script file with the EDIT statement:

EDIT file_name

Now paste the entire script command list into your new file and save it.

set colsep ,
set headsep off
set pagesize 0
set trimspool on

spool c:/temp/oracle/output/employee.csv

SELECT
   employee_id,
   first_name,
   last_name,
   email,
   department_id
FROM HR.EMPLOYEES;

spool off

To execute the script, simply use the @ symbol followed by the file name:

@file_name

Your script should be executed and the .csv file created as expected.

Conclusion

This article presents a method of Oracle SQLPlus export to CSV. While the Oracle SQLPlus export to CSV is one way to do it, if you are not interested in writing custom scripts manually, you have a fully automated, easy-to-use alternative– Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.

visit our website to explore hevo[/hevoButton]

Hevo can help you Integrate your data from numerous sources like Oracle and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

SIGN UP for a 14-day free trial and see the difference!

Why don’t you share your experiences with Oracle SQLPlus export to CSV in the comments? We would love to hear from you!