📋

Description

The ORA-29281 error occurs in Oracle Database when attempting to open a file using `UTL_FILE` with an invalid mode. This typically happens when the specified mode is not one of the accepted values ('r', 'a', or 'w').

💬

Error Message

ORA-29281: invalid mode

🔍

Known Causes

3 known causes

⚠️

Incorrect Mode Specifier

The file open mode was specified with a value other than 'r' (read), 'a' (append), or 'w' (write).

⚠️

Typographical Error

A typo in the mode parameter within the `UTL_FILE.FOPEN` call resulted in an unrecognized mode.

⚠️

Case Sensitivity Issue

The mode parameter is case-sensitive; using uppercase 'R', 'A', or 'W' will cause an error.

🛠️

Solutions

4 solutions available

1. Verify Directory Object for UTL_FILE Usage easy

Ensure the `UTL_FILE_DIR` parameter is correctly configured or a `DIRECTORY` object is properly defined and granted.

1

Check the `UTL_FILE_DIR` parameter in your `init.ora` or `spfile`. If it's not set to the desired directory, you'll need to modify it and restart the database (if using `init.ora`). For dynamic changes with `spfile`, use `ALTER SYSTEM`.

SELECT value FROM v$parameter WHERE name = 'utl_file_dir';

2

Alternatively, and generally the preferred method in modern Oracle versions, ensure a `DIRECTORY` object exists for your target file path. Create it if it doesn't.

CREATE OR REPLACE DIRECTORY my_data_dir AS '/path/to/your/files';
GRANT READ, WRITE ON DIRECTORY my_data_dir TO your_user;

3

When using `UTL_FILE` in your PL/SQL code, always reference the `DIRECTORY` object name instead of a raw file path.

l_file_handle := UTL_FILE.FOPEN('MY_DATA_DIR', 'my_output.txt', 'W');

2. Correct File Opening Mode easy

Ensure the correct mode ('R' for read, 'W' for write, 'A' for append) is used when opening files with `UTL_FILE`.

1

Review the `UTL_FILE.FOPEN` call in your PL/SQL code. The third parameter specifies the mode.

-- Correct usage for writing
UTL_FILE.FOPEN('MY_DATA_DIR', 'output.txt', 'W');

-- Correct usage for reading
UTL_FILE.FOPEN('MY_DATA_DIR', 'input.txt', 'R');

-- Correct usage for appending
UTL_FILE.FOPEN('MY_DATA_DIR', 'log.txt', 'A');

2

Common mistakes include using invalid characters or modes like 'RW', 'WRITE', or simply omitting the mode parameter.

-- Incorrect mode example
-- UTL_FILE.FOPEN('MY_DATA_DIR', 'output.txt', 'RW');

3. Check Database User Permissions on Directory Object medium

Verify that the database user executing the `UTL_FILE` operations has been granted the necessary `READ` and/or `WRITE` privileges on the `DIRECTORY` object.

1

Identify the `DIRECTORY` object being used in your `UTL_FILE.FOPEN` call.

-- Example: If your code uses MY_DATA_DIR
SELECT * FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = 'MY_DATA_DIR';

2

Check the privileges granted on that `DIRECTORY` object to the user executing the PL/SQL code.

SELECT grantee, privilege FROM ALL_TAB_PRIVS WHERE table_name = 'MY_DATA_DIR' AND grantee = 'YOUR_USER_NAME';

3

If the user lacks the required privileges, grant them. For writing, you need `WRITE`. For reading, you need `READ`.

GRANT READ ON DIRECTORY MY_DATA_DIR TO YOUR_USER_NAME;
GRANT WRITE ON DIRECTORY MY_DATA_DIR TO YOUR_USER_NAME;

4. Ensure Oracle Process Permissions on the OS File System advanced

Confirm that the operating system user running the Oracle database processes has read/write permissions on the physical directory specified by the `DIRECTORY` object.

1

Identify the operating system path associated with your Oracle `DIRECTORY` object.

-- In SQL*Plus or SQL Developer
SELECT directory_path FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = 'MY_DATA_DIR';

2

Determine the OS user that the Oracle database processes are running as. This is typically `oracle` or a similarly named user.

# On Linux/Unix systems, use ps command
ps -ef | grep pmon

3

On the database server's operating system, verify the permissions of the Oracle user on the directory path. Use `ls -ld` to check directory permissions and `ls -l` to check file permissions.

# Example for Linux/Unix
ls -ld /path/to/your/files
sudo -u oracle ls -l /path/to/your/files/somefile.txt

4

If permissions are insufficient, use `chmod` and `chown` to grant the necessary read/write/execute permissions to the Oracle OS user.

# Example for Linux/Unix (granting read/write/execute to owner and group)
sudo chmod 775 /path/to/your/files
sudo chown oracle:oinstall /path/to/your/files

Oracle Error ORA-29281: Invalid File Mode