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.