05 Oct 2020
Recently I hit an “ORA-29283” or “invalid file operation” error when editing a long Oracle PL/SQL script. A shorter “hello world” PL/SQL script helped me narrow in on my problem.
DECLARE v_file UTL_FILE.file_type; BEGIN v_file := UTL_FILE.fopen('DIR_SHORTNAME', 'FILENAME.txt', 'w'); END; /
ORA-29283: invalid file operation
The error arose from executing a procedure of Tim’s called
.generate() in the main body of my PL/SQL code:
generate('my_oracle_directory_name', 'my_filename.csv', p_query => 'SELECT * FROM sample_table_name');
My colleagues and I could see from the stack trace that it was getting stuck on the part of Tim’s code that read:
IF g_out_type = 'F' THEN l_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767); END IF;
We tried running SQL queries suggested by Ask Tom forums to eliminate “simple” problems:
First, they verified that the “Oracle directory”
SELECT * FROM all_directories WHERE directory_name = 'my_oracle_directory_name';
Next, they verified that the user under which my code was executing had write privileges to the operating system directory represented by the “Oracle directory”
SELECT * FROM all_tab_privs WHERE table_name = 'my_oracle_directory_name' AND grantee = user AND grantee = 'the_executing_oracle_username' AND privilege = 'WRITE';
Those weren’t problems, so it was time to move on to determining whether something had happened to the folder on the operating system or to the operating system’s “Oracle user”’s permissions to write data to that OS folder, as suggested at Ask Tom.
We had to inquire about that with a DBA / sysamin, so I wrote up a 5-line PL/SQL script that could serve as a “hello world” to test if
utl_file.fopen() was working in “write” mode against the directory represented by
my_oracle_directory_name or not:
DECLARE v_file UTL_FILE.file_type; BEGIN v_file := UTL_FILE.fopen('my_oracle_directory_name', 'my_filename.csv', 'w'); END; /
Providing a short script was helpful because the sysadmin & DBA were able to quickly watch my script change from “not working” to “working” as the sysadmin debugged and fixed what turned out to be casualties of some recent filesystem “improvements,” without worrying about the inner workings of Tim Hall’s code.