UTL_FILE: Writing a File to the File System


Nothing fancy here, I just want to be able to write a file to a directory on the file system.

CREATE DIRECTORY test_dir AS ‘/temp’;

 

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!’ );

Advertisements

One thought on “UTL_FILE: Writing a File to the File System

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s