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.
(outputdir IN VARCHAR2
,filename IN VARCHAR2
,headerline IN VARCHAR2
,refcursor IN SYS_REFCURSOR
-- 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;
-- note: don't open the file unless we actually get some
-- records back from the cursor
BULK COLLECT INTO varr
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);
-- Write the batch of records to the file
FOR i IN 1..varr.COUNT LOOP
UTL_FILE.put_line (outf, varr(i));
IF UTL_FILE.IS_OPEN (outf) THEN
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';
OPEN refcursor FOR
select '"' || name || '"'
|| ',"' || address || '"'
|| ',' || TO_CHAR(dob,'DD-Mon-YYYY')
(outputdir => OUTPUTDIR
,filename => FILENAME
,headerline => HEADERLINE
,refcursor => refcursor
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.