Check Whether File Exists on the Server Using PLSQL

There are different packages using which you can check whether a file exists on the database / application server. The different ways are

1. Using UTL_HTTP from Web Server
2. Using UTL_FILE from Database Server
3. Using DBMS_LOB from Database Server

1. You can also use UTL_HTTP package to check whether a file exists on a web server from the database.

SET SERVEROUTPUT ON
DECLARE
  url       VARCHAR2(256) := 'http://www.oracle.com/index1.html';
  username  VARCHAR2(256);
  password  VARCHAR2(256);
  req       UTL_HTTP.REQ;
  resp      UTL_HTTP.RESP;
BEGIN
  req := UTL_HTTP.BEGIN_REQUEST(url);
  IF (username IS NOT NULL) THEN
    UTL_HTTP.SET_AUTHENTICATION(req, username, password);
  END IF;
  resp := UTL_HTTP.GET_RESPONSE(req);
  DBMS_OUTPUT.PUT_LINE('response -->' || resp.status_code);
END;
/

If the file exists you get response as 200 else you get 404.


2. To check whether a file exists you can use UTL_FILE.FGETATTR  procedure as shown below.

conn sys as sysdba
grant create directory to scott;


conn scott/tiger
create directory TEST_DIR as '/home/oracle'; -- TEST_DIR will point to the directory where the file will be available, test.txt is the filename


set serveroutput on;
DECLARE
 ex    BOOLEAN;
 flen  NUMBER;
 bsize NUMBER;
BEGIN
  utl_file.fgetattr('TEST_DIR', 'test.txt', ex, flen, bsize);


  IF ex THEN
    dbms_output.put_line('File Exists');
  ELSE
    dbms_output.put_line('File Does Not Exist');
  END IF;
  dbms_output.put_line('File Length: ' || TO_CHAR(flen));
  dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));
END fgetattr;
/

3. You can also use DBMS_LOB.FILEEXISTS package also as shown below.

conn sys as sysdba
grant create directory to scott;


conn scott/tiger
create directory TEST_DIR as '/home/oracle'; -- TEST_DIR will point to the directory where the file will be available, test.txt is the filename


set serveroutput on;
DECLARE
   book_file_loc      BFILE := NULL;
   book_file_exists   BOOLEAN := FALSE;


BEGIN
   book_file_loc := BFILENAME( 'TEST_DIR', 'test.txt' );
   book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;


   IF book_file_exists
   THEN
      DBMS_OUTPUT.PUT_LINE
         ('test.txt exists in TEST_DIR directory');
   ELSE
      DBMS_OUTPUT.PUT_LINE
         ('test.txt does not exist in TEST_DIR directory');
   END IF;
END;
/