spooling data to csv file

[Surek]

Hi,
I want to spool data from my query to a csv file.
i have done it.
My query returns more than 31,000 rows.
It takes more than 1 hour to transfer data to the csv file.

Can anyone please help me to write code which reduces the time to transfer data.

Thanks.

[amitpatel66]

Hi,
I want to spool data from my query to a csv file.
i have done it.
My query returns more than 31,000 rows.
It takes more than 1 hour to transfer data to the csv file.

Can anyone please help me to write code which reduces the time to transfer data.

Thanks.

Couple of suggesstions:

1. If you have toad, run the query in toad and SAVE the data as CSV

or

2. Make use of UTL_FILE package to write the data to the file

[Surek]

Couple of suggesstions:

1. If you have toad, run the query in toad and SAVE the data as CSV

or

2. Make use of UTL_FILE package to write the data to the file

I cant use UTL_FILE package. I dont have permissions to change the init.ora file.

Am using toad and now doing the data transfer manually. but i want that functionality to be incorporated in the code.

[amitpatel66]

I cant use UTL_FILE package. I dont have permissions to change the init.ora file.

Am using toad and now doing the data transfer manually. but i want that functionality to be incorporated in the code.

Raise a request with your DBA to modify the init.ora file.

[cheguvera]

Hi,

YOu need not use UTL_FILE to create csv file.

You can simply write a sql file like,

set lines 1000 pages 0 trimspool on
set colsep , =========> this sets the default column saperator in sql output to comma(,)
spool your_file_name
SELECT......
;
spool off
exit

Also, do some analysis. If the query takes long time to execute? or the writting process takes time?
I don't think the writting would take much time, if its around 30000 lines.

Cheers