Home > IT Answers > Development > exporting data to a .csv file in a stored procedure
Kritchard
0 pts.
 exporting data to a .csv file in a stored procedure
SQL, SQL Server
I need to write the results of a query to a .csv file and ftp it from a stored procedure. The query part is no problem but I can't find anything on opening, writing to and ftp'ing the .csv file. Any ideas?

Software/Hardware used:
ASKED: September 22, 2005  11:43 AM
UPDATED: September 26, 2005  11:52 AM
RELATED QUESTIONS
Answer Wiki:
The UTL_FILE Package has methods for creating the file on the operating system's file system. I don't know anyway to ftp from within Oracle, but Oracle does have UTL_SMTP, so you could email it! 8^)) Seriously though, the way I handle it is to have a job awake on the operating system that finds new files to ftp and then ftp's them. ~MrO~
Last Wiki Answer Submitted:  September 22, 2005  12:10 pm  by  MrOracle   0 pts.
All Answer Wiki Contributors:  MrOracle   0 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

What you really need is the ability to execute Operating System commands from PL/SQL. You can find useful code on this from AskTom on Oracle site or Metalink.

The particular implementation that I have uses Java store proc. This works well.

Oadegoke
 0 pts.
 

Sorry, I forgot to mention that I am using MS SQL Server.

Kritchard
 0 pts.
 

You also forgot to mention what version of MS SQL Server you’re using, but start by looking into using BCP and format files in MSDN. BCP handles the opening and writing of the file; the format file defines it as a csv.

For the ftp part, go here http://www.sqlteam.com/item.asp?ItemID=6002

DaveInAZ
 0 pts.