CODEWITHSUNDEEP
oracle-databasebatch-filesqlplusexport-to-csv
Charan
Charan

Reputation: 11

.sql file not returning the column headers in csv file

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

Upvotes: 1

Views: 6308

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

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.

Upvotes: 3

Rahul
Rahul

Reputation: 77926

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

Upvotes: 1

Related Questions