Oracle SQLPlus Export To CSV Data: A Comprehensive Guide

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.  Its important to consider that during an Oracle export to CSV, considerations like delimiters (commas, tabs, etc.) and handling of special characters are important.

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.

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.

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, enabling 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.

1. 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

1a. 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

1b. iSQL*Plus

Image Source

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

1c. 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.

Methods for Oracle SQLPlus Export to CSV

Method 1: Oracle SQLPlus Export to CSV using Hevo Data

The migration of Oracle SQLPlus export to CSV can be done easily using an automated data pipeline tool like Hevo. 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. Hevo Data is a very robust and intuitive solution to accomplish the same.

Learn more about Hevo

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates flexible data pipelines to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Steps To Export Oracle Database Using Hevo Data

Step 1: Configure Oracle as your Source

• Click PIPELINES in the Navigation Bar.
• Click + CREATE in the Pipelines List View.
• In the Select Source Type page, select Oracle as your source.
• In the Configure your Oracle Source page, specify the connection settings for your Oracle Source by providing the URL, username, password, and other credentials.

Step 2: Choose your Destination

• Configure the destination data warehouse from Hevo’s list of destination connectors (such as S3, Redshift, Snowflake, etc.) and start moving data instantly!

You have now successfully completed the Oracle SQLPlus export to CSV.

Method 2: Oracle SQLPlus Export to CSV using the 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 CSV data. While powerful, the Oracle export to CSV command line tool necessitates familiarity with SQL queries to tailor your data export precisely. Before beginning the steps to use the SPOOL command, there are a few configurations that need to be set.

• Step 1: Set The Configurations
• Step 2: Run The Query

Before you start Oracle SQLPlus export to CSV, 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.

Step 1: 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.
• trim pool is set to on to remove trailing whitespace.

Step 2: 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.

Another example of using the Spool command

SQL*PLus will store any query’s output in the designated file while SPOOL is in operation.

Consequently, spool is the next command to enter:

spool file_path

Moving on a little bit, you also need to use the spool off command to stop spooling after inserting your query in order to terminate the file output:

spool off 

Insert the Query

Inserting your query comes last, following the modification of the parameters and the start of spool. We are exporting every book from our bookshelf for our basic example.

SELECT 
title,
primary_author 
FROM
books;

After completing your query expression with a semicolon, type the previously specified spool off command.

Using a Script File as a Tip

It is advised to enter all the settings into a new script file that you can run in SQL*Plus with a single command, as opposed to typing each line by hand.

In a new script file, add the following line: EDIT

EDIT file_name

Now save your new file after pasting the complete script command list in. Below is the complete script that we used as an example.

set colsep ,
 set headsep off
 set pagesize 0
 set trimspool on
 set linesize 2
 set numwidth 5
spool books.csv
SELECT 
title, 
primary_author 
FROM 
books;
spool off

Simply use the @ symbol and the file name to run the script:

@file_name

Your script ought to run and produce the anticipated.csv file. 

That’s all; SQL*Plus has created a new text file with the answers to your query. 

Outputting a Query to a File

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

• 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.

There’s no need to change the first few settings. But here’s a brief explanation of each setting.

• colsep: It is the separator character used to divide your columns. This is a single comma in a .csv file.
• headsep: It is the header row separator character (if one is required). We won’t output the header row in this example.
• pagesize: It refers to the number of lines per page. This relatively ancient option is designed for printing without too many lines on the page. We don’t need pages when we export to a file. Therefore, we set the value to 0. If you choose to show the header row, set pagesize to a relatively large amount so that the header row appears only once rather than “per page.”
• trimspool: When set to “on,” merely removes trailing whitespace.

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
SQLPlus to Output a Text File
spool MyText.txt;
SQLPlus to Output File Name from a Table
SPOOL ON
SPOOL XXXXX.bkp
SELECT * FROM my_table WHERE field1='value';
SPOOL OFF
SQLPlus to Output a File bigger than 4 GB
spool on
>set heading off
>set trimsp on pages 0

The above example employs SQLPlus CSV output for various use cases.

Insert the Query

You can now finally insert your query. For the purpose of this demonstration, we’re outputting all employees from the HR.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. You can combine the Oracle export table to CSV command line with scripting languages to automate recurring data extraction tasks.

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.

Limitations of Oracle SQLPlus Export to CSV

When exporting data from a database to a CSV file, several limitations can arise, impacting the process’s efficiency, accuracy, and overall utility. These limitations include:

• Complex Data Types and Structures. CSV may not support some data types and structures that are available in other formats. This can cause problems when migrating data, and may require manual or customized solutions.
• Character Set Conversion Issues: CSV does not have a standard way of indicating the character encoding of the data. To avoid corruption of special characters , you should use UTF-8 encoding for your CSV files.
• Export Size Limitations: Some platforms limit how many records can be exported to a CSV file simultaneously. 
• Lack of update: CSV files are static and do not reflect the changes in the database. To keep your CSV files updated and accurate, you should export them regularly or schedule a download job.
• Lack of access & credential controls:  CSV files are offline and do not have any security features. This means that anyone who has access to the CSV file can view, modify, or share it without your consent. 
• Lack of database actions/computing power:  CSV files are flat and do not have any database functions or capabilities. This means processing and computing the data from a CSV file can be difficult and slow.

Use Cases of Oracle SQLPlus Export to CSV

• Advanced Analytics: CSV Files allow for complex data analysis on Oracle DB data, providing valuable insights.
• Data Consolidation: Using CSV File Destination to sync multiple data sources with Oracle DB can centralize your data and set up a change data capture process, avoiding any discrepancies in your operations.
• Historical Data Analysis: Syncing data to CSV Files allows for longer data retention and analysis of historical trends beyond Oracle DB’s limits.
• Data Security and Compliance: CSV File Destination offers secure data transfer for Oracle DB data to CSV format, enabling advanced data governance and compliance management.
• Scalability: CSV File Destination is ideal for growing businesses with expanding Oracle DB data, as it can handle large volumes of data without affecting performance.
• Data Science: You can use Oracle DB data in CSV file format to apply machine learning models for predictive analytics and customer segmentation.
• Reporting and Visualization: Oracle DB has reporting tools, but Tableau, PowerBI, and Looker can connect to CSV File Destination for more advanced business intelligence options. 

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 150+ pre-built Integrations that you can choose from.

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.

Frequently Asked Questions