ORA-29283 hello world
Tags: sql, database
Published: 2020-10-08 20:38:34

---
title: ORA-29283 hello world
description:  "A quick test for Invalid File Operation"
published: true
date: 2020-10-05 15:00:00 UTC
tags: sql, database
canonical_url: https://katiekodes.com/ora-29283-test/
---

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.

```sql
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](https://oracle-base.com/dba/script?category=miscellaneous&file=csv.sql) into a [block of anonymous PL/SQL](https://dev.to/katiekodes/anonymous-pl-sql-to-write-a-csv-from-sql-73n).

{% link https://dev.to/katiekodes/anonymous-pl-sql-to-write-a-csv-from-sql-73n %}

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:

```sql
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:

```sql
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](https://asktom.oracle.com/pls/apex/asktom.search?tag=getting-error-as-ora-29283-invalid-file-operation) to eliminate "simple" problems:

First, they verified that the "Oracle directory" `my_oracle_directory_name` existed:

```sql
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`:

```sql
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:

```sql
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.