trying to check whether the file I want to read exists or not.
Here are another approaches:
Using BFILE
and fileexists
function of dbms_lob
package:
create or replace function FileExists(
p_DirName in varchar2, -- schema object name
p_FileName in varchar2
) return number
is
l_file_loc bfile;
begin
l_file_loc := bfilename(upper(p_DirName), p_FileName);
return dbms_lob.fileexists(l_file_loc); -- 1 exists; 0 - not exists
end;
Using fgetattr
function of utl_file
package:
create or replace function FileExists(
p_DirName in varchar2, -- schema object name
p_FileName in varchar2
) return number
is
l_fexists boolean;
l_flen number;
l_bsize number;
l_res number(1);
begin
l_res := 0;
utl_file.fgetattr(upper(p_DirName), p_FileName, l_fexists, l_flen, l_bsize);
if l_fexists
then
l_res := 1;
end if;
return l_res;
end;