I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS.
How do I do it?
You could also use the following, although it does introduce spaces between fields.
set colsep , -- separate columns with a comma set pagesize 0 -- No header rows set trimspool on -- remove trailing blanks set headsep off -- this may or may not be useful...depends on your headings. set linesize X -- X should be the sum of the column widths set numw X -- X should be the length you want for numbers (avoid scientific notation on IDs) spool myfile.csv select table_name, tablespace_name from all_tables where owner = 'SYS' and tablespace_name is not null;
Output will be like:
TABLE_PRIVILEGE_MAP ,SYSTEM SYSTEM_PRIVILEGE_MAP ,SYSTEM STMT_AUDIT_OPTION_MAP ,SYSTEM DUAL ,SYSTEM ...
This would be a lot less tedious than typing out all of the fields and concatenating them with the commas. You could follow up with a simple sed script to remove whitespace that appears before a comma, if you wanted.
Something like this might work...(my sed skills are very rusty, so this will likely need work)
sed 's/\s+,/,/' myfile.csv