CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
bnix
bnix

Reputation: 245

Spool Command: Do not output SQL statement to file

I am wanting to output a Query to a CSV file and am using the below as a small test;

spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

but the output has the actual select statment 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

Is there a way to prevent this? I tried SET Heading Off thinking that might do it, but it did not change. I am using SQL Developer an running as script.

Thanks Bruce

Upvotes: 17

Views: 152365

Answers (6)

Amit Sanghvi
Amit Sanghvi

Reputation: 21

Just for anyone who stumbles upon this (after 7+ years) ...

This works for me ... adapt it to your way of invoking sqlplus.

sqlplus -s / as sysdba 2>&1 > /dev/null <<EOF
  whenever sqlerror exit sql.sqlcode;
  set echo off 
  set verify off
  set feedback off
  set term off
  set head off
  set pages 1000
  set lines 200

  spool /tmp/whatever.output
  
  ### Your_SQL_statement_here ###
  example: select sysdate from dual;
  example: select * from V\$LOGFILE;

  spool off
EOF

-s flag here is the key to not displaying the SQL statements when runing a script.

Upvotes: 1

mdabdullah
mdabdullah

Reputation: 626

My shell script calls the sql file and executes it. The spool output had the SQL query at the beginning followed by the query result.

This did not resolve my problem:

set echo off

This resolved my problem:

set verify off

Upvotes: 3

nikhil kodipaka
nikhil kodipaka

Reputation: 1

Exec the query in TOAD or SQL DEVELOPER

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

Save in .SQL format in "C" drive

--- x.sql

execute command

---- set serveroutput on
     spool y.csv
     @c:\x.sql
     spool off;

Upvotes: 0

Avrajit
Avrajit

Reputation: 230

You can directly export the query result with export option in the result grig. This export has various options to export. I think this will work.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

Unfortunately SQL Developer doesn't fully honour the set echo off command that would (appear to) solve this in SQL*Plus.

The only workaround I've found for this is to save what you're doing as a script, e.g. test.sql with:

set echo off
spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

And then from SQL Developer, only have a call to that script:

@test.sql

And run that as a script (F5).

Saving as a script file shouldn't be much of a hardship anyway for anything other than an ad hoc query; and running that with @ instead of opening the script and running it directly is only a bit of a pain.

A bit of searching found the same solution on the SQL Developer forum, and the development team suggest it's intentional behaviour to mimic what SQL*Plus does; you need to run a script with @ there too in order to hide the query text.

Upvotes: 26

Walter Mitty
Walter Mitty

Reputation: 18940

set echo off
spool c:\test.csv 
select /*csv*/ username, user_id, created from all_users;
spool off;

Upvotes: 0

Related Questions