DB and Server Setups
1.
The directory path
on the application node will need to be mounted/shared on the database node.
2. The directory path
is readable/writable from the database node.
3.
The directory path
should have required permissions.
DB Level - Verification SQL
SELECT directory_name,
directory_path
FROM sys.dba_directories
WHERE directory_name = 'CUSTOM_DIR';
SELECT grantee,
privilege,
table_name
FROM sys.dba_tab_privs
WHERE table_name = 'CUSTOM_DIR';
Server Level - Verification Command
ls -altr CUSTOM_DIR_PATH
Basic Code with Important Exceptions
DECLARE
lf_utl_file_id UTL_FILE.file_type;
lv_dba_dir_name VARCHAR2 (30) := 'CUSTOM_DIR';
lv_data_file_name VARCHAR2 (80) := 'outbound_file.txt';
lv_utl_file_mode VARCHAR2 (1) := 'w'; -- 'w', 'r'
lv_data_line VARCHAR2 (2000);
BEGIN
IF NOT UTL_FILE.is_open (lf_utl_file_id)
THEN
lf_utl_file_id :=
UTL_FILE.fopen (lv_dba_dir_name,
lv_data_file_name,
lv_utl_file_mode,
32767);
END IF;
lv_data_line := ''; -- Extract Data
UTL_FILE.put_line ( lf_utl_file_id, lv_data_line);
UTL_FILE.fclose (lf_utl_file_id);
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.read_error
THEN
raise_application_error ( -20055, 'Read 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');
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');
END;
