UTL_FILE: Writing a File to the File System

This is more for me that it is for you as I always seem to forget how to do this. Next time I'll simply search my site for the example.

Nothing fancy here, I just want to be able to write a file to a directory on the file system. This directory must be a database object (until java is used anyway).

So...

CREATE DIRECTORY test_dir AS '/temp';

No, I'm not doing this on Linux (yet), this is my c:\temp directory.

Here's the procedure:

CREATE OR REPLACE
PROCEDURE write_to_file
  ( p_dir IN VARCHAR2,
    p_filename IN VARCHAR2,
    p_file IN CLOB )
IS
  l_file_handle UTL_FILE.FILE_TYPE;
  l_loops INTEGER := 1;
  l_max_linesize NUMBER := 32767;
  l_start_position NUMBER := 1;
  l_source VARCHAR2(32767);
BEGIN
  l_file_handle := utl_file.fopen
                    ( location => p_dir,
                      filename => p_filename,
                      open_mode => 'w',
                      max_linesize => l_max_linesize );

  LOOP
    l_source := SUBSTR( p_file, l_start_position, l_max_linesize );
    EXIT WHEN l_source IS NULL;

    utl_file.put_line
      ( file => l_file_handle,
        buffer => l_source );

    l_start_position := l_loops + ( l_max_linesize * l_loops );
    l_loops := l_loops + 1;

  END LOOP;
  utl_file.fclose( l_file_handle );

EXCEPTION
  WHEN others THEN
    utl_file.fclose( l_file_handle );
    RAISE;
END write_to_file;
/
show errors

Simple enough right? Running it is easy from SQL*Plus.

EXEC write_to_file( 'TEST_DIR', 'testing.sql', 'HELLO WORLD!' );

I've done it 20 or 30 times, but each time I have to relearn it...next time I won't!