Your error is because fileexists returns a NUMBER, not boolean (don't ask me why). But anyway..

BFILES work with directory objects. If you want to do it by "file path" and "file name", that'll not work as you have it coded. Consider:


ops$tkyte@ORA920> declare
2 l_bfile bfile := bfilename( '/tmp/', 'foo.dat' );
3 begin
4 if ( dbms_lob.fileexists( l_bfile ) = 1 )
5 then
6 dbms_output.put_line( 'Yes' );
7 else
8 dbms_output.put_line( 'No' );
9 end if;
10 end;
11 /
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEEXISTS operation
ORA-06512: at "SYS.DBMS_LOB", line 485
ORA-06512: at line 4


ops$tkyte@ORA920> create or replace directory MY_DIR as '/tmp'
2 /

Directory created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
2 l_bfile bfile := bfilename( 'MY_DIR', 'foo.dat' );
3 begin
4 if ( dbms_lob.fileexists( l_bfile ) = 1 )
5 then
6 dbms_output.put_line( 'Yes' );
7 else
8 dbms_output.put_line( 'No' );
9 end if;
10 end;
11 /
Yes

PL/SQL procedure successfully completed.


Now, if you can create a directory object -- that would be easiest -- however we can use utl_file to achieve this in a fashion:

ops$tkyte@ORA920> create or replace function file_exists( p_fname in varchar2 ) return boolean
2 as
3 l_file utl_file.file_type;
4 begin
5 l_file :=
6 utl_file.fopen( substr( p_fname, 1, instr(p_fname,'/',-1) ),
7 substr( p_fname, instr( p_fname, '/', -1)+1 ), 'r' );
8
9 utl_file.fclose( l_file );
10 return TRUE;
11 exception
12 when utl_file.invalid_path then return FALSE;
13 when utl_file.invalid_operation then return FALSE;
14 end;
15 /

Function created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
2 if ( file_exists( '/tmp/foo.dat' ) )
3 then
4 dbms_output.put_line( 'Yes' );
5 else
6 dbms_output.put_line( 'No' );
7 end if;
8 end;
9 /
Yes

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> begin
2 if ( file_exists( '/tmp/foo2.dat' ) )
3 then
4 dbms_output.put_line( 'Yes' );
5 else
6 dbms_output.put_line( 'No' );
7 end if;
8 end;
9 /
No

PL/SQL procedure successfully completed.