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.

AUSOUG Perth Conference 2011 Day Two
3 Reasons to Hate Hibernate

Leave a Reply

Your email address will not be published / Required fields are marked *