Procedure to export table to multiple csv files

Question

I am working with an Oracle database - the only way I have to access it is through SQL Developer. Part of my work involves exporting large tables to csv files to pass to another group. Since this is mostly babysitting the system, I've been looking for a way to automate the export process.

What I would like is to have a procedure like so:

PROCEDURE_EXAMPLE(table_in in VARCHAR2, file_out in VARCHAR2)

where table_in is the table I need to export, and it exports the table to a series of csv files titled "file_out_1.csv" "file_out_2.csv", etc.. each with no more than 5 million rows.

Is it possible to create a procedure like this?

Answer 1

You can using the UTL_FILE package. You can only read files that are accessible from the server on which your database instance is running.

See http://www.devshed.com/c/a/Oracle/Reading-Text-Files-using-Oracle-PLSQL-and-UTLFILE/ and Oracle write to file

Answer 2

I was just posting an answer here: Procedure to create .csv ouput

Using the UTL_FILE package is often not an option, because it can only create files on the server and is rather limited.

If you can only use SQL Developer, you can change the window to a command window and then you can run the commands just as I described in the other thread.

In the SQL Window right click -> Change Window to -> Command Window

set term off ...
spool c:\tmp\t\txt
select ...
spool off