It is probably apparent from my job title that my role is far from a dba. I am, however, a frequent user of Oracle SQL Developer. I work in mass appraisal and use SQL Developer to select, update, insert, delete, etc., on a regular basis. I would like to bring some automation to some of the select statements that I run most frequently and spool the results to a .csv file. An example of this is a select statement for identifying recently sold properties that need to be reviewed.
The following command [using the Windows Command Prompt] has been stored as a scheduled task [using Windows Task Scheduler]:
G:\>sqlplus username/password@connection @G:\SALES_VALIDATION\bat_files\weekly_salesval_rev.sql
Weekly_salesval_rev.sql is the script containing the spool commands and select statement. I have included a link so you can view the script.
What command [or commands] can I incorporate so that the data will be formatted appropriately [and include column headings] for review in Excel? While there should be approximately 21 columns of data, the results are currently displaying in a scattered fashion within the first 3 columns of the .csv file.
If you need any other detail, please let me know. Also, if you would suggest or recommend other approaches to automating frequently run SQL select statements, let me know and I would be glad to look into those alternatives. Thank you for your time and help!
SQL Plus Release 22.214.171.124.0
Oracle Database 19c Standard Edition 2 Release 126.96.36.199.0
Oracle SQL Developer 188.8.131.52
Windows Version 10.0.17763.1577
From 12.2, SQL*Plus has a
set markup csv on
command. As the name suggests, this returns the output in CSV format:
SQL> set markup csv on
SQL> select * from co.customers fetch first 10 rows only;
This should solve your Excel formatting woes. https://blogs.oracle.com/opal/fast-generation-of-csv-and-json-from-oracle-database
SQLcl has a similar option with the
set sqlformat csv
command. This includes a whole bunch of other formats for your exporting pleasure https://www.thatjeffsmith.com/archive/2015/02/a-quick-4-1-trick-set-sqlformat/