UTL_FILE Write Example

The below example illustrates how to write a line to a file using UTL_FILE.

1. Create Directory on your server (ex: Linux, Unix)
2. Give writable permissions to the directory created. If you register this code as a concurrent program then you need to make sure your applmgr user has write permissions to this directory.
3. Create DIRECTORY object in database using below command. This is different from the physical directory you created in step1.

1	CREATE OR REPLACE DIRECTORY ERPS_OUT_DIR AS ‘/home/haritha’

You can see the existing directory objects using below query

1	select * from dba_directories;

UTL_FILE Write Example:

1 2 3 4 5

DECLARE l_file_handler UTL_FILE.FILE_TYPE; l_txt VARCHAR2(1000) := ‘Demo to write to a file using UTL_FILE’; l_file_name VARCHAR2(50) := ‘ERPSFILE.txt’; BEGIN

–Open the file in Write mode
l_file_handler := UTL_FILE.FOPEN(‘ERPS_OUT_DIR’,l_file_name,’W’);

–Write to the file
UTL_FILE.Put_LINE(l_file_handler,l_txt);

–Once the writing is done, close the file
IF UTL_FILE.IS_OPEN(l_file_handler) THEN
UTL_FILE.FCLOSE(l_file_handler);
END IF;

EXCEPTION

WHEN UTL_FILE.invalid_mode THEN
raise_application_error (-20051, ‘Invalid Mode Parameter’);

WHEN UTL_FILE.invalid_path THEN
raise_application_error (-20052, ‘Invalid File Location’);

WHEN UTL_FILE.invalid_filehandle THEN
raise_application_error (-20053, ‘Invalid Filehandle’);

WHEN UTL_FILE.invalid_operation THEN
raise_application_error (-20054, ‘Invalid Operation’);

WHEN UTL_FILE.write_error THEN
raise_application_error (-20055, ‘Write Error’);

WHEN UTL_FILE.internal_error THEN
raise_application_error (-20057, ‘Internal Error’);

WHEN UTL_FILE.charsetmismatch THEN
raise_application_error (-20058, ‘Opened With FOPEN_NCHAR But Later I/O Inconsistent’);

WHEN UTL_FILE.file_open THEN
raise_application_error (-20059, ‘File Already Opened’);

WHEN UTL_FILE.invalid_maxlinesize THEN
raise_application_error (-20060, ‘Line Size Exceeds 32K’);

WHEN UTL_FILE.invalid_filename THEN
raise_application_error (-20061, ‘Invalid File Name’);

WHEN UTL_FILE.access_denied THEN
raise_application_error (-20062, ‘File Access Denied By’);

WHEN UTL_FILE.invalid_offset THEN
raise_application_error (-20063, ‘FSEEK Param Less Than 0’);

WHEN OTHERS THEN
raise_application_error (-20099, ‘Unknown UTL_FILE Error’||sqlerrm);

END;