I recently had to perform a database load, an action whereby you take a query result/s from one database store and then migrate that to another database store. My idea was to automate it as this was usually done manually with a lot of clicky clicky and the query ran for a 20mins, so certainly this was not fun in the slightest bit.

Ideally what you can do with clicky clicky can be done using some form of tool or program, thankfully SQLPlus a GUI application which I use to interact with Oracle Databases has a Commandline component and I could use that to run queries against Oracle databases from a terminal/shell. Once I got valid documentation that supported that, I got to work.

#!/bin/bash

FILE="file.csv"

sqlplus -silent xxxxx/xxxxxx//xxxxx:1521/xxxx <<EOF
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 200
SET FEEDBACK OFF
SPOOL $FILE

SELECT * FROM EMP;

SPOOL OFF
EXIT
EOF

The resulting bash script above, once it runs creates a file called file.csv and contained in this file is the result of our query SELECT * FROM EMP; . Now let’s try and understand the scripts and the options set.

SET PAGESIZE 50000 => Set this to a much bigger value. This value indicates the number of lines per page. The header line will get printed on every page. In order to avoid this, set it to a bigger value so that the header appears only once.

SET COLSEP “,” => Setting the column separator to “,”. With this setting, the list displayed by the SELECT clause will be comma-separated. This is the most important setting of this script.

SET LINESIZE 200 => The number of characters per line. The default is 80 which means after 80 characters, the rest of the content will be in the next line. Set this to a value that is good enough for the entire record to come in a single line.

SET FEEDBACK OFF => When a select query is executed, a statement appears at the prompt, say “25 rows selected”. In order to prevent this from appearing in the CSV file, the feedback is put off.

SPOOL $FILE => Spool command records the session queries and results into the file specified. In other words, this will write the results of the query to the file.

SELECT * FROM EMP => The query which gives the entire table contents of EMP. If only a part of the table is desired, the query can be updated to get the desired result.

SPOOL OFF => To stop writing the contents of the SQL session to the file.

 

And that’s all folks.

Speak with an expert on our team to discuss Cloud solutions tailored to your business.

You’ll receive free recommendations tailored to your business.