Skip to Main Content
  • Questions
  • Getting error as ORA-29283: invalid file operation

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ankit.

Asked: July 23, 2018 - 6:53 pm UTC

Last updated: June 25, 2019 - 8:09 am UTC

Version: 12.1.0.1

Viewed 10K+ times! This question is

You Asked

Hi,

we have created this anonymous block to capture the data using DB link and write into CSV file. The details are as below,

--Table
create table emp (empno number)
/

--insert 

insert into emp vaules (10)
/
insert into emp vaules (20)
/
insert into emp vaules (30)
/
insert into emp vaules (40)
/
insert into emp vaules (50)
/
Commit
/

---Anonymous Block

DECLARE
   CURSOR c1
   IS
      SELECT empno FROM emp@testDB;

   v1        c1%ROWTYPE;
   z1        VARCHAR2 (2000);
   z2        VARCHAR2 (2000);
   fhandle   UTL_FILE.file_type;
BEGIN
   DBMS_OUTPUT.put_line ('WRITE');
   fhandle := UTL_FILE.fopen ('TEMP_DIR1', 'Report.csv', 'W');

   FOR I IN C1
   LOOP
      z1 := i.empno;
      Z2 := Z2 || CHR (10) || Z1;
   END LOOP;

   UTL_FILE.PUT_LINE (fhandle, Z2);
   UTL_FILE.fclose (fhandle);
END;
/


Getting below error,

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 12


please suggest.

Thanks

and we said...

Either you're trying to write to a directory that doesn't exist or you have insufficient privileges.

Verify that the directory object exists:

select * from all_directories
where  directory_name = 'TEMP_DIR1';


And that you have write privileges on it:

select * from all_tab_privs
where  table_name = 'TEMP_DIR1'
and    grantee = user
and    privilege = 'WRITE';


If these are fine, check that the folder exists on the OS and the OS Oracle user has write access to it.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ankit Bhatt, July 24, 2018 - 12:35 pm UTC

hi,

I have checked both the queries. The directory is present and having write privileges also.

How can we check "the OS Oracle user has write access to it"?

please suggest.

thanks


Chris Saxon
July 24, 2018 - 12:50 pm UTC

Look on the database server in the location the directory points to. Does it exist? Does the user which the Oracle software runs under have write access to this?

Ankit Bhatt, July 25, 2018 - 5:04 am UTC

yes,the user which the Oracle software runs under have write access still same invalid file operation issue is coming.
Chris Saxon
July 25, 2018 - 10:21 am UTC

Break the problem down. Strip parts out of your code until you see what's throwing the exception. When you've narrowed it down, let us know your findings and we'll see how we can help.

Ankit Bhatt, July 26, 2018 - 9:47 am UTC

It's resolved, thanks.

Migs Isip, June 24, 2019 - 3:09 pm UTC

Hi, I have almost the same issue. I confirmed that APPS and PUBLIC have the sufficient privileges:

SQL> select GRANTEE from all_tab_privs
where  table_name = 'EXT_TAB_DATA'
and    privilege = 'WRITE';  2    3

GRANTEE
--------
APPS
PUBLIC

SQL>


And I also confirmed that the actual Directory is defined:

SQL> select * from all_directories
where  directory_name = 'EXT_TAB_DATA';  2

OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
EXT_TAB_DATA
/u01/app/oracle/DEV/SAMPLE
            0
SQL>


Below is the privileges of the directory /u01/app/oracle/DEV/SAMPLE

[appldev-run ~]$ ls -l /u01/app/oracle/DEV
total 24
-rw-r--r-- 1 appldev appldev 6473 Jun 19 15:10 EBSapps.env
drwxr-xr-x 5 appldev appldev 4096 Jun  4 10:13 fs1
drwxr-xr-x 5 appldev appldev 4096 Jun  7 16:26 fs2
drwxr-xr-x 4 appldev appldev 4096 May 22 12:32 fs_ne
-rw------- 1 root    root       0 Sep 18  2018 nohup.out
drwxrwxrwx 2 oracle  appldev 4096 Jun 25 02:31 SAMPLE


When I try to write a simple UTL_FILE command below:

declare 
  fHandle  UTL_FILE.FILE_TYPE;
begin
  fHandle := UTL_FILE.FOPEN('EXT_TAB_DATA', 'test_file', 'w');

  UTL_FILE.PUT(fHandle, 'This is the first line');
  UTL_FILE.PUT(fHandle, 'This is the second line');
  UTL_FILE.PUT_LINE(fHandle, 'This is the third line');

  UTL_FILE.FCLOSE(fHandle);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
    RAISE;
end;
/

it results into an error:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 14
29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.


And I even tried reading from an External Table:

CREATE TABLE sample_ext
( sample1 varchar(10) )
organization external (
  default directory EXT_TAB_DATA
  location ('test.txt')
  );
/  
select  *
from    sample_ext;  


It results into the error below:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04001: error opening file /u01/app/oracle/DEV/SAMPLE/SAMPLE_EXT_62883.log
29913. 00000 -  "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.

I confirmed that the file is there and has data:

[appldev-run@xxxx ~]$ cd /u01/app/oracle/DEV/SAMPLE
[appldev-run@hamlfinappdev ~]$ cat test.txt
1
1
1
1
[appldev-run@xxxx ~]$


What do I need to do to read and write to this directory?
Chris Saxon
June 25, 2019 - 8:09 am UTC

There's a permission error somewhere - the oracle OS user is unable to read/write to the directory in question for some reason.

I see you've already asked this on SO and have a thorough answer there ;)

Resolved Permissions Issue

A reader, June 26, 2019 - 12:17 pm UTC

Yes I was able to get permissions and grants on the directory with the help of the DBA. The answer in SO also helped. Pasting the link to the SO answer to anyone who might need it in the future: https://stackoverflow.com/questions/56739653/cant-read-files-using-external-tables-or-write-files-using-utl-file-using-pl-sq

More to Explore

PL/SQL

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