.sql file not returning the column headers in csv file

Question

The below code is in batch file script(.bat file) which calls the sql file.

del C:\SREE\csvfile.csv    
sqlplus SERVERNAME/Test123@ldptstb @C:\SREE\sree.sql    
set from_email="SENDER_EMAIL_ID"     
set to_email="TO_EMAIL_ID"     
set cc_email="CC_EMAIL_ID"    
set email_message="Csv file from application server"    
set body_email=C:\SREE\sree.txt    
set sendmail=C:\Interface\sqlldr\common\SENDMAIL.VBS    
set interface_log=C:\SREE\csvfile.csv    
cscript %sendmail% -F %from_email% -T %to_email% -C %cc_email% -S %email_message% -B %body_email%  -A %interface_log% -O "ATTACHFILE" -A %body_email% -O "FILEASTEXT"
exit

This below content in .sql file code which executes the SQL Query and stores the data into csv file:

set pagesize 0    
set heading on    
set feedback off    
set trimspool on    
set linesize 32767    
set termout off    
set verify off    
set colsep ","    
spool C:\SREE\csvfile.csv    
SELECT Name, ID, Email, Role, Status FROM csvfile    
exit

The output is stored in csv file and getting this file in email.

But theproblem is I am not getting the Column Names in the csv file. I had tried in many scenarios to get the names as a cloumn headings in csv file.

Anyone please help me out with the code to get the column names in the csv file.. thanks in advance...

I believe you are using Oracle. From your command which uses sqlplus — Rahul
– Rahul, Commented Mar 29, 2014 at 20:35
I am using Oracle tool and I am able to get the data in the csv file in the email attachment. But I am not able to get the Column names in the csv file. — Charan
– Charan, Commented Mar 29, 2014 at 20:45

Alex Poole · Accepted Answer · 2014-03-29 23:10:06Z

3

When you set pagesize 0 headings are suppressed:

SET PAGES[IZE] {14 | n}
Sets the number of lines on each page of output. You can set PAGESIZE to zero to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

That's the case even if you then explicitly set headings on.

You can either set pagesize to something very large instead, or possibly more helpfully as you probably don't really want the separator line of dashes, generate them yourself with:

PROMPT Name,ID,Email,Role,Status

... before your select statement.

answered Mar 29, 2014 at 23:10

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charan Over a year ago

Hi Alex Poole, It works fine with the PROMPT. Really really Thanks alot for your kind help.

Rahul · Accepted Answer · 2014-03-29 20:49:19Z

1

Use GENERATE_HEADER configuration setting amd set it to Yes like

SET GENERATE_HEADER = 'Yes'

See this related thread here https://community.oracle.com/thread/2325171?start=0&tstart=0

answered Mar 29, 2014 at 20:49

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

.sql file not returning the column headers in csv file

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related