Write REF CURSOR to file
This is just a very simple procedure that makes writing a lot of files using UTL_FILE much simpler. It can be used to easily create simple CSVs or fixed-length files.
I’ve shown it here in a package body; it’s up to you to create a package spec.
To use it, all you need to do is create a query that concatenates all the data into a single string up to 4000 characters long. If you’re writing a CSV you need to concatenate commas and quotes as appropriate.
CREATE OR REPLACE PACKAGE BODY packagename AS -- If no records are found in the cursor, no file is created. PROCEDURE write_cursor_to_file (outputdir IN VARCHAR2 ,filename IN VARCHAR2 ,headerline IN VARCHAR2 ,refcursor IN SYS_REFCURSOR ) IS -- increase to make faster but use more memory; -- decrease to use less memory but run slower BATCHSIZE CONSTANT INTEGER := 100; TYPE varr_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; varr varr_type; outf UTL_FILE.FILE_TYPE; BEGIN -- note: don't open the file unless we actually get some -- records back from the cursor LOOP FETCH write_cursor_to_file.refcursor BULK COLLECT INTO varr LIMIT BATCHSIZE; EXIT WHEN varr.COUNT = 0; -- We have some records to write. Have we opened the file yet? IF NOT UTL_FILE.IS_OPEN (outf) THEN outf := UTL_FILE.fopen (file_location => write_cursor_to_file.outputdir ,file_name => write_cursor_to_file.filename ,open_mode => 'A' ,max_linesize => 4000); UTL_FILE.put_line (outf, write_cursor_to_file.headerline); END IF; -- Write the batch of records to the file FOR i IN 1..varr.COUNT LOOP UTL_FILE.put_line (outf, varr(i)); END LOOP; END LOOP; CLOSE write_cursor_to_file.refcursor; IF UTL_FILE.IS_OPEN (outf) THEN UTL_FILE.fclose (outf); END IF; END write_cursor_to_file; PROCEDURE sample IS OUTPUTDIR CONSTANT VARCHAR2(100) := 'MY_DIR_NAME'; FILENAME CONSTANT VARCHAR2(100) := 'my_file_name.csv'; HEADERLINE CONSTANT VARCHAR2(4000) := 'Name,Address,Date of Birth'; refcursor SYS_REFCURSOR; BEGIN OPEN refcursor FOR select '"' || name || '"' || ',"' || address || '"' || ',' || TO_CHAR(dob,'DD-Mon-YYYY') from persons; write_cursor_to_file (outputdir => OUTPUTDIR ,filename => FILENAME ,headerline => HEADERLINE ,refcursor => refcursor ); END sample; END packagename; /
It appends to the file if it finds it. This makes it easy to write the result of several queries to the same file.
Note: if you’re on Oracle 8i or earlier, you’ll need to add a replacement for SYS_REFCURSOR, e.g. TYPE my_sys_refcursor IS REF CURSOR; either at the top of the package, or if you want to make the write_cursor_to_file procedure public, put the type definition in your package spec.