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.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.