I am unable to get output from Spool command: Do not output SQL statement to file.

Question

I really need to output a Query to a CSV file and I tried the following test;

spool c:\test.csv 

select /*csv*/ username, user_id, created from all_users;

spool off;

But the output has the select statement as the first line

but the output has the actual select statement as the first line

> select /*csv*/ username    user_id     created from all_users

USERNAME    USER_ID CREATED

REPORT  52  11-Sep-13

WEBFOCUS    51  18-Sep-12

 

How can I prevent this? I tried SET Heading Off thinking that might do it, but it could not fix it. I am using SQL Developer and running a script. Any help will be appreciated.

Answer 1

To prevent the select statement from appearing as the first line in your CSV output, you can modify your script as follows:

SET HEADING OFF

SET FEEDBACK OFF

SET PAGESIZE 0

SET ECHO OFF

SET VERIFY OFF

SET TERMOUT OFF

SET TRIMSPOOL ON

SPOOL c:\test.csv

SELECT /*csv*/ username, user_id, created FROM all_users;

SPOOL OFF

Here's an explanation of the additional SET commands:

- SET HEADING OFF: This command turns off the column headings in the output.

- SET FEEDBACK OFF: This command suppresses the "X rows selected" message.

- SET PAGESIZE 0: This command sets the page size to 0, which means no pagination.

- SET ECHO OFF: This command turns off the echoing of commands in the output.

- SET VERIFY OFF: This command turns off the verification of the substitution variables.

- SET TERMOUT OFF: This command turns off the terminal output.

- SET TRIMSPOOL ON: This command trims the trailing spaces from each line in the spool file.

By using these SET commands, you should be able to generate a clean CSV file without the select statement appearing as the first line.

Answer 2

Actually, SQL Developer doesn't fully honor the set echo off command twitch solves this in SQL*Plus.

But you can try this out:

set echo off

spool c:\test.csv 

select /*csv*/ username, user_id, created from all_users;

spool off;

And then call that script from SQL developer

@test.sql

And run that as a script (F5).

You can easily save this script file.

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.

Answer 3

To prevent the select statement from being included in the output CSV file, you can use the SQL*Plus command `SET ECHO OFF` before running your query. This will disable the echoing of the SQL statements in the output.

Here's an example of how you can modify your script:

sql

SET ECHO OFF

SET FEEDBACK OFF

SET HEADING OFF

SET PAGESIZE 0

SET LINESIZE 1000

SPOOL C:\test.csv

SELECT /*csv*/ username, user_id, created FROM all_users;

SPOOL OFF

By adding `SET ECHO OFF` to your script, the select statement will no longer be displayed in the output CSV file. The other `SET` commands in the script are additional settings to customize the output format.

Note that `SET HEADING OFF` is already included in your initial script, but it doesn't affect the echoing of the select statement. By combining it with `SET ECHO OFF`, you can achieve the desired outcome of excluding the select statement from the CSV output.

Answer 4

To prevent the SQL query statement from appearing as the first line in the CSV output, you can modify your script slightly. Here's an updated version of your script:

set feedback off

set heading off

set pagesize 0

set linesize 1000

set trimspool on

set colsep ','

spool c:\test.csv

select username, user_id, created from all_users;

spool off;

In this modified version, the following changes have been made:

- `set feedback off` - This prevents the SQL*Plus feedback from being displayed.

- `set heading off` - This disables column headers.

- `set pagesize 0` - This sets the page size to zero, effectively removing any headers and footers.

- `set linesize 1000` - This sets a large linesize to accommodate longer column values.

- `set trimspool on` - This trims the trailing whitespace from each spooled line.

- `set colsep ','` - This sets the column separator to a comma (`,`) for CSV formatting.

By applying these settings, you should get a clean CSV output without the SQL query statement at the beginning.