Search code examples
oraclesqlplus

Remove initial and trailing blank line in SQL *Plus SPOOL (SET MARKUP CSV)

I'm trying to use SQL *Plus to export simple SELECTs to .csv files. If there is a better way to do this, I'm open, but I need to use SQL and end in .csv and be able to click something to run the whole thing without manual steps.

The output from this script begins and ends with blank lines (x'0D' and x'0A').
Sample output

Is there any way to remove them, especially the first blank line? I can do it in the next step of my process, but it has the potential to introduce errors, and it seems like there ought to be an easy way to do it from SQL *Plus.

Here's my attempt:

SET MARKUP CSV ON DELIMITER "," QUOTE OFF
SET HEADING ON
SET SHOWMODE OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET NEWPAGE NONE
SET PAGESIZE 0
TTITLE OFF
set echo off
set trimspool on
set trimout on
SPOOL "C:\Users\me\Downloads\sql-export2.csv" REPLACE
SELECT * 
FROM my_table;
SPOOL OFF;
EXIT;

I don't think these lines are needed (and some aren't supposed to work with SET MARKUP CSV), but trying to show everything that I've tried that doesn't work.

SET NEWPAGE NONE
SET PAGESIZE 0
TTITLE OFF
set echo off
set trimspool on
set trimout on

I have this saved to a .sql file which I run from a command prompt using the sqlplus command. The SQL *Plus version is 12.2.0.1.0 because my drivers are old. If version 19 might help, I could try to resolve my installer problems.

I'm new to SQL *Plus and don't really understand the use of ; characters, so please correct my syntax if I'm doing something wrong there.

Update: I did confirm the leading space is introduced by SET HEADING ON. I suspect this is a "feature" of headings that I can do nothing about.

Solution

  • Generating CSV files is much easier using SQLcl than SQLPlus. 99% of SQLPlus commands can be used with SQLcl. To generate a CSV file in SQLcl, you can still use SPOOL to create your file, SET FEEDBACK OFF to prevent the "X rows selected." message from showing after the query, and SET SQLFORMAT CSV to easily generate CSV output from a query. Using this format, the first line of the query results will be the column headers for your CSV.

    Here is an example of what it would look like

    SQLcl: Release 23.3 Production on Mon Jan 08 11:59:56 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Mon Jan 08 2024 11:59:58 -05:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

SQL> set feedback off
SQL> set sqlformat csv
SQL> spool test.csv
SQL> select * from dba_objects where rownum <= 10;
"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED","APPLICATION","DEFAULT_COLLATION","DUPLICATED","SHARDED","CREATED_APPID","CREATED_VSNID","MODIFIED_APPID","MODIFIED_VSNID"
"SYS","I_FILE#_BLOCK#","",9,9,"INDEX",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_OBJ3","",38,38,"INDEX",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_TS1","",45,45,"INDEX",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_CON1","",51,51,"INDEX",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","IND$","",19,2,"TABLE",10-FEB-20,22-JUL-23,"2020-02-10:10:26:41","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","CDEF$","",31,29,"TABLE",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",1,"","METADATA LINK","","Y","N","USING_NLS_COMP","N","N",,,,
"SYS","C_TS#","",6,6,"CLUSTER",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",5,"","METADATA LINK","","Y","N","","N","N",,,,
"SYS","I_CCOL2","",58,58,"INDEX",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_PROXY_DATA$","",24,24,"INDEX",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
"SYS","I_CDEF4","",56,56,"INDEX",10-FEB-20,10-FEB-20,"2020-02-10:10:26:41","VALID","N","N","N",4,"","NONE","","Y","N","","N","N",,,,
SQL> spool off
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0