Oracle : PL/SQL to test writing to an OS directory from the database.

Many time I had developer hitting errors as such
ERROR : ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation


Below is an sql I got from somewhere that can be used to test out writing / reading from the directory.

( Also check that you have set the parameter utl_file_dir correctly )

SET SERVEROUTPUT ON
DECLARE
fid UTL_FILE.FILE_TYPE;
v VARCHAR2(32767);

PROCEDURE recNgo (str IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('UTL_FILE error ' str);
UTL_FILE.FCLOSE (fid);
END;

BEGIN

/* Change the directory name to one to which you at least
THINK you have read/write access.
*/

fid := UTL_FILE.FOPEN ('{change directory name here}', '{change existing file name here}', 'R');
UTL_FILE.GET_LINE (fid, v);
dbms_output.put_line (v); UTL_FILE.FCLOSE (fid);

fid := UTL_FILE.FOPEN ('{change directory name here}', '{change new file name here}', 'W');
UTL_FILE.PUT_LINE (fid, v); UTL_FILE.FCLOSE (fid);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN recNgo ('invalid_path');
WHEN UTL_FILE.INVALID_MODE THEN recNgo ('invalid_mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN recNgo ('invalid_filehandle');
WHEN UTL_FILE.INVALID_OPERATION THEN recNgo ('invalid_operation');
WHEN UTL_FILE.READ_ERROR THEN recNgo ('read_error');
WHEN UTL_FILE.WRITE_ERROR THEN recNgo ('write_error');
WHEN UTL_FILE.INTERNAL_ERROR THEN recNgo ('internal_error');

END;/