Re: how can I check if a file exists with the UTL_FILE package

From: Jacek Dobosz <jdob_at_nospam.ping.at>
Date: 1998/01/27
Message-ID: <34ce4058.8656921@news.ping.at>#1/1

On Tue, 27 Jan 1998 09:51:58 -0600, erik_at_softman.com.pl wrote:

>Hi,
>
>I try to build some PL/SQL routines for FILE I/O. One of these routines
>is to check if a file exists. I wrote the following routine to achieve
>this but I run into some strainge error:
>
>FUNCTION check_file (fpath IN VARCHAR2, ffile IN VARCHAR2) RETURN BOOLEAN
>IS file_handle UTL_FILE.FILE_TYPE; BEGIN file_handle :=
>UTL_FILE.FOPEN(fpath, ffile, 'R'); RETURN UTL_FILE.IS_OPEN(file_handle);
>EXCEPTION WHEN OTHERS THEN RETURN FALSE; END;
>
>When I call this function with an existing file and the right path
>everything works without any problems. However, when I specify a file
>that doesn't exists I get the error ORA-03113 end-of-file on
>communication channel. For some strainge reason this error doesn't raise
>an exception and therefore I am not able to handle this error.
>

Well, it looks like your code crashes the session. End-of-communication channel means that the server process is dead. You should find some ora-0600 errors or equivalent in the alert_<sid>.ora file on server now.

Anyway, try to rewrite your code.
I have found the similar function in my old software. It's not as compact as yours, but it works fine on NT and UNIX:

FUNCTION check_file (fpath IN VARCHAR2, ffile IN VARCHAR2) RETURN BOOLEAN is
h_fd utl_file.file_type;
e_fatal exception;
Begin
  BEGIN
   h_fd := utl_file.fopen(fpath, ffile, 'r');   EXCEPTION

   WHEN utl_file.invalid_path THEN
    RAISE e_fatal;
   WHEN utl_file.invalid_mode THEN
     RAISE e_fatal;
   WHEN utl_file.invalid_operation THEN
     RAISE e_fatal;
   WHEN OTHERS THEN
     RAISE e_fatal;

  END;
  utl_file.fclose( h_fd );
  return true;
exception
  when e_fatal then
    return false;
  when others then
    raise_application_error( -20000, 'your error message' ); end;

jacek

+-----------------------------------------+
| if you want to replay, use jdob_at_ping.at |
| or remove nospam from the address       |
+-----------------------------------------+       

Received on Tue Jan 27 1998 - 00:00:00 CST