Trying to read the file using utl_file method

Question

I am trying to see if this file exists. But I'm getting this error message. I have already checked the privileges I got them. But this file is on the server side so is there something I am missing

DECLARE
  vInHandle  utl_file.file_type;

BEGIN

  vInHandle := utl_file.fopen('IMG_UTL_DIR', 'image-file.csv', 'R');

  IF utl_file.is_open(vInHandle) THEN

      dbms_output.put_line('The File exists');

  Else

      dbms_output.put_line('The File not  exists');
  END IF;
END fopen;

Errors:

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

No, fopen takes the directory object name as a varchar, so it should be quoted. Does that directory object exist, do you have privileges on that object, does the underlying directory exist in the server operating system, does Oracle have access to that; and does the file actually exist? You said you're testing for that, but if it doesn't then it will error. I can't remember off-hand if you're allowed to pass the mode in uppercase - the docs only show lower. — Alex Poole
@AlexPoole - I stand corrected. At our shop the DBA's refuse to let us use directory objects ("They're a potential security risk". WTF?!?!?!? But who am I to argue... :-/) so we're still opening files with directory paths - please don't ask me how this is "better" or "more secure" - such things are beyond the ken of mere mortals such as myself. — Bob Jarvis - Reinstate Monica

Alex Poole Alex Poole · Accepted Answer · 2014-08-21T07:30:43

If the file does not exist then you will get that error. With your code, when the file exists you will get:

anonymous block completed
The File exists

But when the file does not exist you will get:

Error report -
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 6
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.

Note the 'a file or directory that does not exist' part of the error description. You cannot test for the file's existence like this. As far as I'm aware there is no direct way to test for a file being there; you would have to attempt to open the file and catch the exception. For example:

DECLARE
  vInHandle  utl_file.file_type;
  eNoFile    exception;
  PRAGMA exception_init(eNoFile, -29283);
BEGIN
  BEGIN
    vInHandle := utl_file.fopen('IMG_UTL_DIR', 'image-file.csv', 'R');
    dbms_output.put_line('The File exists');
  EXCEPTION
    WHEN eNoFile THEN
      dbms_output.put_line('The File not  exists');
  END;
END fopen;
/

anonymous block completed
The File not  exists

But the ORA-29283 exception can mean other things as well, as the description says, so it doesn't necessarily mean the file is not there - it could be there but not accessible for some other (permission-related) reason. You would also be masking the location of the actual error to some extent, and if you had multiple file operations in the block then you'd either have to wrap each one in its own begin/exception/end sub-block to specify the error, or lose the actual error point.

You're probably better off just letting the exception be raised and reported naturally, rather than catching it and replacing it with a dbms_output message which might not be retrieved and displayed by the client anyway.

Trying to read the file using utl_file method

1 Answers