How to export data from Oracle database using SQL*Plus command line?

Oracle database is a collection of data which treated as a unit.  Oracle Database is the first database designed for enterprise grid computing which supports to connect hardware and software components on demand to meet the changing needs of businesses.

SQL*Plus is an interactive and batch command-line query tool that is installed with every Oracle Database installation. It can access through the following ways.

• Command-line user interface
• Windows Graphical User Interface (GUI)
• iSQL*Plus web-based user interface

In this article, I am focusing on how can we interact with the Oracle database using the Command-line user interface.

How to connect to the Oracle database using the SQL*Plus command line?

You  can connect into the Oracle database using following command by replacing <username>, <password>, <protocol>, <hostAddress>, <port>, <serviceName> tags by corresponding values.

sqlplus '<username>/<password>@(DESCRIPTION=(ADDRESS=(PROTOCOL=<protocol>)(Host=<hostAddress>)(Port=<port>))(CONNECT_DATA=(SERVICE_NAME=<serviceName>)))'

Sample connection String

sqlplus 'admin/admin123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=orclmdb.o2.com)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=EMP)))'

How to retrieve information in CSV format using the SQL*Plus command line?

When we execute a select statement in the SQL*Plus, the output is not given in a well-formatted way and it is difficult to read and will be shown as follows.

You can retrieve the information in CSV format by executing the following query before the select query. Then the output will be shown in CSV format.

set markup csv on;

Then the output will be shown in CSV format and it will be easy to read.

How to export data from Oracle Database using  SQL*Plus command line?

Suppose you have to export all the records in a table named “employee” in CSV format using  SQL*Plus command line.

First, log into the Oracle database and run the following SQL  command to create a file named “Employee”.

EDIT employee

The SQL*Plus EDIT command allows you to invoke the text editor of your choice to use in editing and saving SQL statements.

Then you have to add the following lines to manually alter the settings one time prior to the select query and file generation.

set markup csv on;

set colsep ,

set headsep off

set pagesize 0

set trimspool on

set linesize #

set numwidth #

Let's get an brief idea about these setting parameter.

• colsep : the separator character used to split the columns. For a .csv file, this is a simple comma.
• Headsep: the separator character for the header row. In this example we are not outputting the header row and because of that its value is set as off.
• Pagesize: Specifies the number of lines per page. In this Pagesize value is set to 0 since the output is send to a file
• Trimspool: use to remove trailing whitespace.
• Linesize : the # value should be the total number of output columns in the resulting query.
• numwidth : is the column width (number of character spaces) used when outputting numeric values.

As the next step, we need to insert the spool command. The spool command is used to store the output of any query to the specified file. Since I need to save the Employee table content into a CSV file named employee I added the following command.

spool employee-data.csv

After that, we have to add the select query to retrieve information from all the columns in the table.

select * from emp.employee;

Or you can add the select query by specifying only several columns. Please note that you have to set the value in set linesize # according to the number of columns in the result.

As the final command, you have to add the spool off command to close the file output.

spool off

After adding all the SQL commands, file content should be as follows.

You can save the file content and exit by pressing Esc and type :wq! And Enter.

Now you can execute that script by running the following command in the SQL*Plus command line.

@employee

As the result of that execution, you could find a file named employee-data.csv in your working directory with all the content in the employee table.