Error reading a file using UTL_FILE

Question

I am facing some issue in reading a file using utl_file. The same code is working fine in some of the instances and failing in one instance.

Below is the issue:

Code:

procedure del_mul_tags(p_request_id   in number)
is

fileID UTL_FILE.FILE_TYPE;
fileID1 UTL_FILE.FILE_TYPE;
line varchar2(32000);
l_dir varchar2(1000);
l_file varchar2(20);
l_file1 varchar2(20);
l_request_id number;
l_count number := 0;
l_count_t number :=0;
l_write boolean := TRUE;

cursor c_dir is
select substr(OUTFILE_NAME,1,instr(OUTFILE_NAME,'/',-1,1)-1),substr(OUTFILE_NAME,instr(OUTFILE_NAME,'/',-1,1)+1)
from fnd_concurrent_requests
where request_id = p_request_id;

begin

open c_dir;
fetch c_dir into l_dir,l_file;
close c_dir;

execute immediate 'create or replace directory W2_OUT_DIR as ''' || l_dir || '''';

begin

      fileID1 := UTL_FILE.FOPEN ('W2_OUT_DIR', l_file, 'R'); -----Getting the error right here!!!-----------
      loop
      begin
       UTL_FILE.GET_LINE (fileID1, line);

       --Some Logic

       EXCEPTION WHEN NO_DATA_FOUND THEN
       UTL_FILE.FCLOSE(fileID1);
       EXIT;
       end;
       end loop;

       fileID := UTL_FILE.FOPEN ('W2_OUT_DIR', l_file, 'R');

         loop
         begin
           UTL_FILE.GET_LINE (fileID, line);

          --Some Logic

           EXCEPTION WHEN NO_DATA_FOUND THEN
           UTL_FILE.FCLOSE(fileID);
           EXIT;
           end;
         end loop;

         end;

EXCEPTION WHEN OTHERS   THEN
fnd_file.put_line(fnd_file.log,'Error while deleting the xml tags: '||SQLERRM);
end;

................

In this instance file, l_file 'ACHAKR01.23067873' was created through another concurrent process. Its OS user is 'appsofde'. And the unix permissions are as below:

-rw-r--r-- 1 appsofde dba 192092429 jan 27 05:00 ACHAKR01.23067873

The directory W2_OUT_DIR is created and the oracle user apps has EXECUTE,READ,WRITE privileges.

The file exists and the W2_OUT_DIR dir patch is correct.

Exact error coming is:

Error while deleting the xml tags: ORA-29283: invalid file operation 
ORA-06512: at "SYS.UTL_FILE", line 536 
ORA-29283: invalid file operation

Any thoughts here?

Add some debug: `dbms_output.put_line(l_file);` right before the line catching the error. — mmmmmpie, Feb 12 '15 at 13:19
The file I am reading is a XML file, I tried to print some messages before that line. I feel it is something to with directory permissions. I will print the line and see if there are any exceptions. Thanks for your reply. — user3783052, Feb 13 '15 at 06:37

score 0 · Answer 1 · answered Feb 13 '15 at 19:29

You will want to make sure the filesystem is presented to your Oracle DB host (not just your application server). I'm assuming it is otherwise you would get an invalid directory path error instead. I would suggest you double check however and also ensure the oracle account on the DB host has access to read the file in question. The account appsofde sounds as if its likely the application account.

Error reading a file using UTL_FILE

1 Answers1