Skip to content

Instantly share code, notes, and snippets.

@kylelix7
Created June 13, 2017 00:05
Show Gist options
  • Save kylelix7/f13ced7387b874d7b60821aedb534847 to your computer and use it in GitHub Desktop.
Save kylelix7/f13ced7387b874d7b60821aedb534847 to your computer and use it in GitHub Desktop.
csv file from sqlplus
https://chartio.com/resources/tutorials/how-to-write-to-a-csv-file-using-oracle-sql-plus/
How to Write to a CSV File Using Oracle SQL*Plus
DATA TUTORIAL
laptop with example chartio dashboard
Working with Redshift, BigQuery, MySQL, MongoDB, Postgres, IBM DB2, Oracle?
Easily connect your databases and create powerful visualizations and interactive dashboards in minutes.
Try Chartio for Free Today
[SQLPlus](https://docs.oracle.com/cd/B19306_01/server.102/b14357/qstart.htm) is an interactive tool that comes installed with every modern Oracle installation. SQLPlus allows you to go beyond the standard database queries with batches, scripts, and calculations beyond the normal scope of Oracle.
It is even possible, as we’ll explore below – to generate text files, such as .csv, using the output of a particular database query.
Launching SQL*Plus
Depending on your Oracle installation, you may have access to one of many different versions or “modes” in which to run the SQL*Plus application.
SQL*PLUS COMMAND-LINE
If you wish to use SQL*Plus Command-line, you’ll simply issue the sqlplus command from your shell: bash $ sqlplus
This will attempt to connect you to the default database and you’ll be prompted to enter your credentials to authenticate yourself.
In the event you need to connect to a different database or use a different user (schema), try the following, replacing your own values as necessary:
$ sqlplus schema@//machine.domain:port/database
ISQL*PLUS
If you have access to it, you may wish to use iSQL*Plus, which is a browser-based version of the SQL*Plus command-line tool.
This is accomplished by simply visiting the iSQL*Plus URL for your database and installation. The exact URL will vary, but it is typically in the following format: http://machine_name.domain:port/isqlplus
SQL*PLUS FOR WINDOWS
If you’re using Windows, there is also a Windows GUI version of SQL*Plus, which can typically be launched from your start menu: Start > Programs > Oracle > Application Development > SQL Plus.
Outputting a Query to a file
Now that you’re connected to SQL*Plus we can begin creating our file.
MODIFY SQL*PLUS CONFIGURATION
The first step is to configure some SQL*PLus system settings using the SET statement.
For this example, we’ll be manually altering these settings one time prior to our query and file generation, but if desired, you can change the defaults of various settings in your User Profile, located in the login.sql file.
set colsep ,
set headsep off
set pagesize 0
set trimspool on
The first few settings you typically won’t want to change, but we’ll briefly explain what each accomplishes.
colsep is the separator character used to split your columns. For a .csv file, this is a simple comma.
headsep is the separator character for the header row (if you require one). In this example we’re not outputting the header row, so we’ll leave this off.
pagesize is the number of lines “per page.” This is a slightly archaic setting that is intended for printing without having too many lines per page. With a value of 0, we don’t use pages since we’re outputting to a file. If you elect to show the header row, set pagesize to a very large number (larger than the expected number of record results in the query), so your header row will only show up one time rather than once “per page.”
trimspool set to on simply removes trailing whitespace.
Now the final two settings will need to be altered depending on your query.
set linesize #
set numwidth #
For linesize, the # value should be the total number of output columns in your resulting query.
numwidth is the column width (number of character spaces) used when outputting numeric values.
SPOOL COMMAND
Note: The SPOOL command is unavailable in the browser-based SQL*Plus version, iSQL*Plus. To generate files while using iSQL*Plus, change the necessary preference settings to directly output to a file.
With our settings taken care of, now we must tell SQL*Plus to output a file. This is accomplished using the SPOOL statement.
While SPOOL is active, SQL*PLus will store the output of any query to the specified file.
Therefore, the next command to enter is spool:
spool file_path
Skipping ahead slightly, after your query is inserted, you also need to halt spool so the file output is closed by using the spool off command:
spool off
INSERT THE QUERY
The last step after the settings are modified and spool is running is to insert your query. For our simple example, we’re outputting all books from our books table.
SELECT
title,
primary_author
FROM
books;
Don’t forget the semi-colon to close out your query statement, then enter the aforementioned spool off command.
That’s it, you’ve generated a new text file with the results of your query using SQL*Plus.
Tip: Using a Script File
Rather than manually entering every line, it is suggested to enter all the settings into a new script file that you can execute in SQL*Plus in 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. The full contents of our example script can be found below.
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
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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment