Skip to content

ORA-29280: invalid directory path

14/11/2012

Having migrated to a new database server, I encountered this error. After some investigation I checked the system parameter utl_file_path. This lists the directories where files can be created from Oracle.

During migration I had set this parameter to a location where part of our system generates files which are then FTP’d to a different server. This worked fine. The problem arose when I tried to run a procedure which generates some shell and SQL files on the filesystem.

We are not using an Oracle Directory object, rather a direct reference to the filesystem location.

ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 33
ORA-06512: at “SYS.UTL_FILE”, line 436
ORA-06512: at “”, line 123
ORA-06512: at line 2

The line in question, line 123, is the following:

sde2shp_outputFile := utl_file.fopen (areaspath, sde2shpAll, ‘W’, 32000);

areaspath is a variable containing the directory path and sde2sdpAll is a variable containing the filename to write to.

Cause:
An attempt to read from a file or directory that does not exist; or file or folder access is denied by the operating system.

Action:
To assist with resolving this issue, carry out these two checks initially:
1) Verify that the file or folder is present and the reference to it is correct.
2) Check that appropriate filesystem permissions are granted on the object.
In my instance, the previous checks/requirements passed okay so then I checked the Oracle system parameter utl_file_dir.

SELECT * FROM v$parameter WHERE name=’utl_file_dir’;

The column of interest is “value“, this was set to a specific location on the database server filesystem which although worked for one process did not for another.
I changed this to all directories:

ALTER SYSTEM SET utl_file_dir=’*’ SCOPE=spfile;

This then required me to down and up the database in order to pick up the change. Obviously this should be done out of normal working hours, or during a scheduled maintenance window.
When the database is available again you can confirm the change using the same SQL as before:

SELECT * FROM v$parameter WHERE name=’utl_file_dir’;

This should confirm that the “value” is now “*“.

I then executed the procedure again and the issue was cleared.

Advertisements

From → ORA Errors, Oracle

One Comment
  1. Wow, superb blog layout! How long have you been blogging for?

    you make blogging look easy. The overall look of your website is excellent, as well as the content!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: