Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
831 views
in Technique[技术] by (71.8m points)

oracle - 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:est.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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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:est.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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...