SQL 检查文件是否存在？

Check if a file exists?

提问by eSS92

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

试图检查我要读取的文件是否存在。

回答by Nick Krasnov

Here are another approaches:

这是另一种方法：

1. Using BFILEand fileexistsfunction of dbms_lobpackage:

   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 fgetattrfunction of utl_filepackage:

   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;
   

1. 包的用途BFILE及fileexists作用dbms_lob：

   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. 包的使用fgetattr功能utl_file：

   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;
   

回答by Joachim Isaksson

Creating a function that checks if a file exists is fairly easy by just trying to open it and catching any exceptions (this example function taken from AskTom)

创建一个检查文件是否存在的函数相当容易，只需尝试打开它并捕获任何异常（此示例函数取自AskTom）

CREATE OR REPLACE FUNCTION file_exists(p_fname IN VARCHAR2) RETURN BOOLEAN
AS
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN(SUBSTR( p_fname, 1, instr(p_fname,'/',-1) ),
                           SUBSTR( p_fname, instr( p_fname, '/', -1)+1 ), 'r' );   
  UTL_FILE.FCLOSE( l_file );
  RETURN TRUE;
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH      THEN RETURN FALSE;
  WHEN UTL_FILE.INVALID_OPERATION THEN RETURN FALSE;
END;
/

Then you can just use;

然后你可以使用;

IF ( file_exists( 'MED_LIST_19_OCT_12.csv' ) )
...

回答by Andre

Use UTL_FILE.FGETATTRfunction. This function is designed specifically for this purpose.

使用UTL_FILE.FGETATTR功能。此功能是专门为此目的而设计的。

Syntax:

句法：

UTL_FILE.FGETATTR(
   location     IN VARCHAR2, 
   filename     IN VARCHAR2, 
   fexists      OUT BOOLEAN, 
   file_length  OUT NUMBER, 
   block_size   OUT BINARY_INTEGER);

Example:

例子：

DECLARE
  fexists      BOOLEAN;
  file_length  NUMBER;
  block_size   BINARY_INTEGER;
BEGIN

    UTL_FILE.FGETATTR('MY_ORA_DIRECTORY', 'my_file_name.csv', fexists, file_length, block_size);
    IF fexists THEN
       -- Do something
       -- ...
    END IF;

END IF;

Oracle documentation: https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70915
One more useful link: https://www.foxinfotech.in/2018/09/how-to-check-if-file-exists-in-pl-sql.html

Oracle 文档：https: //docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70915 还有
一个有用的链接：https: //www.foxinfotech.in/2018/09/how-to-check-if -file-exists-in-pl-sql.html