sqloracle-databaseplsql

Check if a file exists?


trying to check whether the file I want to read exists or not.


Solution

  • Here are another approaches:

    1. 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;
      
    2. 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;