Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

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.

  v_file UTL_FILE.file_type;
  v_file := UTL_FILE.fopen('DIR_SHORTNAME', 'FILENAME.txt', 'w');

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

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:

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:

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:

  v_file UTL_FILE.file_type;
  v_file := UTL_FILE.fopen('my_oracle_directory_name', 'my_filename.csv', 'w');

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.

--- ---