Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, e.

Asked: October 12, 2020 - 4:44 pm UTC

Answered by: Connor McDonald - Last updated: October 14, 2020 - 4:04 am UTC

Category: PL/SQL - Version: 4.1.5.21.78

Viewed 100+ times

You Asked

I'm trying to use the utl_file.fopen command (in this case to write a new file out, using SQL Developer v4.1.5.21.78 with Oracle and PL/SQL v12.2.0.1.0, and I just can't seem to shake the ORA-29823 - "invalid file operation" error.

It seems pretty clear it is a permissions issue, but I simply can't track it down. I've made sure to do the following:
> Establish the required directory in All_Directories table.
> Ensured the Oracle user has Write permissions on that folder in All_tab_Privs (It seems to default to include Read/Write/Execute).
> Ensure that I've used the specified directory name in the utl_file.fopen command.
> Ensured the folder exists in the server OS
> Ensured that the Oracle user account (in Windows) has full privileges on the subject folder. (When logged in as that user, I can create/edit/delete files at will.)

If I understand correctly, I shouldn't expect to need to modify init.ora with the versions I have, but maybe that's the missing piece? If adjustments are not necessary in init.ora, then I just don't know where else to look to make this command work.

Excerpts from the code are as follows:

I manually ran this to create the directory entry:
CREATE OR REPLACE DIRECTORY POLY_FILES AS 'C:\Approved_Locations\polygons';


Then, in the code attempting to write out there exists:

DECLARE
  filPoly utl_file.file_type;

  filPoly := utl_file.fopen('POLY_FILES', strFileName, 'w');


Where strFileName is just the name of the file, which I know is valid because I output this value to the Dbms Output right before this line.

thanks for any advice/insight.
e


and we said...

A common cause of this is how the database is running under Windows.

If you go into Services on Windows, check the "Logon as" property. Often that is not the user you might expect it to be (it be local system or a service account depending on version).

You need to ensure that *this* account has access on the OS directory.

Another option to test is go very very simple, eg

- create a directory (say) C:\TEMP
- At the OS level give Full access to "Everyone"
- Then test *that* directory with UTL_FILE in simplest form

DECLARE
  filPoly utl_file.file_type;
BEGIN
  filPoly := utl_file.fopen('MY_DEMO_DIR', 'test.dat', 'w');


If that doesn't work, its time to call Support. But assuming it does, you work from there to establish where things are failing on your true directory.

and you rated our response

  (1 rating)

Reviews

Unexpected solution

October 13, 2020 - 12:03 pm UTC

Reviewer: e

So, after trying your “keep it simple” suggestion, I found the problem. It was not a permissions issue!

I made the newbie mistake of using a char variable to store my file names. Apparently this wreaks all kind of havoc with added spaces etc. Once I used varchar2, the problems went away.

Thanks for your suggestions!
Connor McDonald

Followup  

October 14, 2020 - 4:04 am UTC

Glad we could help

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.