ORA-29283 hello world

05 Oct 2020 🔖 sql databases
💬 EN ( Lire cet article en français )

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;
/

I was working on transforming Tim Hall’s Oracle PL/SQL “CSV” package into a block of anonymous PL/SQL. Unfortunately, when its .generate() procedure was executed, it returned an error:

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” my_oracle_directory_name existed:

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” my_oracle_directory_name:

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.