Procedure for exporting a table to multiple csv files

Question

I work with an Oracle database - the only way to access it through SQL Developer. Part of my work involves exporting large tables to csv files to move to another group. Since this is basically a nanny system, I was looking for a way to automate the export process.

I would like to have a procedure like this:

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 called "file_out_1.csv", "file_out_2.csv", etc., each of which contains no more than 5 million rows.

Is it possible to create such a procedure?

Answer 1

You can use 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 the file

Answer 2

I just posted the answer here: Procedure for creating .csv ouput

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

If you can only use SQL Developer, you can change the window in the command window, and then you can run the commands in the same way as I described in another thread.

In the SQL Window, right-click → Change window in → Command window

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