Just to add to the question what if you had to check for multiple files. Is there anything that will allow me to put all the filenames or should I just repeat DBMS_LOB.FILEEXISTS for each.

I needed to check for the existence of a graphics file which was referenced in the database. The UTL_FILE open of course bombed on me. The bfiles and dbms_lob.fileexists saved me. Thanks a lot!

The simple solution doesn't really seem to work. I have sent a detailed question to Tom and when I receive his reply and test it I will update this questions page.

Hi Tom,
   I have one observation about the utl_file.fgetattr procedure. I observed different behaviors on Solaris and RH Linux.

The test script
----------------
set serveroutput on
spool /tmp/filecheck.txt
create or replace directory TEST_DIR as '/tmp';

host touch /tmp/filecheck.chk

declare
  lv_check_file_exist boolean;
  lv_a number;
  lv_b number;
begin
dbms_output.put_line ('test when file is available');
utl_file.fgetattr ('TEST_DIR', 'filecheck.chk', lv_check_file_exist, lv_a, lv_b );
if lv_check_file_exist then
dbms_output.put_line('file exists');
end if;
if not lv_check_file_exist then
dbms_output.put_line('file does not exist');
end if;
if lv_check_file_exist is null then
dbms_output.put_line('file check null');
end if;
if lv_check_file_exist is not null then
dbms_output.put_line('file check not null');
end if;
dbms_output.put_line('lv_a-->'||lv_a);
dbms_output.put_line('lv_b-->'||lv_b);
end;
/

host rm /tmp/filecheck.chk

declare
  lv_check_file_exist boolean;
  lv_a number;
  lv_b number;
begin
dbms_output.put_line ('test when file is NOT available');
utl_file.fgetattr ('TEST_DIR', 'filecheck.chk', lv_check_file_exist, lv_a, lv_b );
if lv_check_file_exist then
dbms_output.put_line('file exists');
end if;
if not lv_check_file_exist then
dbms_output.put_line('file does not exist');
end if;
if lv_check_file_exist is null then
dbms_output.put_line('file check null');
end if;
if lv_check_file_exist is not null then
dbms_output.put_line('file check not null');
end if;
dbms_output.put_line('lv_a-->'||lv_a);
dbms_output.put_line('lv_b-->'||lv_b);
end;
/

spool off

------------------------------------------------
------------------------------------------------
===============
Output On Linux
===============
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> !uname -a
Linux magicwand.cts.com 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:32:14 EDT 2005 i686 i686 i386 GNU/Linux

The spool file:-
----------------------
Directory created.

test when file is available
file exists
file check not null
lv_a-->0
lv_b-->4096

PL/SQL procedure successfully completed.

test when file is NOT available
file does not exist
file check not null
lv_a-->
lv_b-->

PL/SQL procedure successfully completed.
--------------------------------------------------
--------------------------------------------------
=================
Output on SunOS
=================
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bi
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for Solaris: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 – Production

$ uname -a

SunOS ctsinpunsun11 5.9 Generic_118558-25 sun4u sparc SUNW,Sun-Fire

The spool file:-
----------------------
Directory created.

test when file is available
file exists
file check not null
lv_a-->0
lv_b-->8192

PL/SQL procedure successfully completed.

test when file is NOT available
file check null
lv_a-->0
lv_b-->0
PL/SQL procedure successfully completed.

I am not sure which one is incorrect since 10.2 documentation does not clarify the expected output. Of course one has to be wrong.

thanks, 

@Anindya

If you look in the $ORACLE_HOME/rdbms/admin/utlfile.sql at the comments:
"...OUT fexists - true or false, for exists or doesn't exist. Note: the following parameters have no meaning if the file doesn't exist, in which case, they return NULL. ..."

So it becomes clear that they are supposed to be NULL. But since they have no meaning unless the file exists, the point is moot, why would you be looking at them? ;)

HTH

Chris