create csv file

If you want headings, then you will need to take out the "heading off" clause, and set pagesize to a value other than zero.  The problem with doing this is that if you set pagesize to, for example 100, then you will get a page heading every 100 rows.  As you only want one page heading then you must set this to zero (which has the effect of saying "no page heading") or set it to a value higher than the number of rows that will be retrieved.  The maximum value it can be set to is 500000.  Another option is to leave page headings off (as in my example above) and to generate the page headings yourself by including a select from dual e.g.

set linesize 4000 pagesize 0 colsep ','
set heading off feedback off verify off trimspool on trimout on

spool mycsvfile.csv

-- Generate column headings
select 'COL1_HEAD','COL2_HEAD','COL3_HEAD','COL4_HEAD',...
from sys.dual;

select col1, col2, col3, col4, .....
from mytab;

spool off

exit;

hi Milleniumaire,
 
Thanks for u  r reply. how and where to open the generated csv file? because I am using sql developer I don't the output file?

Alternatively, if you have access to a tool like TOAD, then when you run sql in the editor window, you can right click on the results page and this will provide you with a context menu with an option to Save As.  You can then chose what format to save your rows in (delimited text or Excel for example).

Assuming you can run a script from SQL Developer, you change the spool command in my example above to specify the folder/directory in which to create the file

Instead of:

spool mycsvfile.csv

Change to:

spool C:\mycsvfile.csv

When you run the script this should create the file mycsvfile.csv in the C:\ folder.

You can then navigate to the folder using Windows Explorer and open it with either Excel or a text editor e.g. Notepad.

Hi, my comments for closing this ...
In ravindran_eee's post (http:#24554303), this is already covered by Milleniumaire's earlier post (http:#24548115).

Most effort was put in by Milleniumaire, and in my opinion he answered the question.  I think that I added some value here, with comments specific to SQL developer.  The points should be split to reflect that.  
split:
Milleniumaire (http:#24547921) - 350 points
Andytw (http:#24548648) - 150 points