Oracle Apps Knowledge Sharing

Query to check whether file exists on server using Oracle PLSQL (DBMS_LOB.FILEEXISTS, UTL_FILE.FGETATTR)

In this post, we have given few approaches to check whether file exists in the directory or not using PLSQL. 

Background:

    1. Create a Directory in the server and place the test file

    2. Create a DBA_DIRECTORY for the same 

Option 1: Using SQL query and DBMS_LOB.FILEEXISTS and BFILENAME

SELECT (CASE DBMS_LOB.FILEEXISTS(BFILENAME('TESTORACLE','test.dat')) 
        WHEN 1 THEN 'FILE_EXISTS'
        ELSE 'FILE NOT FOUND'
        END
        ) testfile,
       (CASE DBMS_LOB.FILEEXISTS(BFILENAME('TESTORACLE','random.dat')) 
        WHEN 1 THEN 'FILE_EXISTS'
        ELSE 'FILE NOT FOUND'
        END
        ) randomfile
 FROM  dual; 

Option 2: Using PLSQL and UTL_FILE.FGETATTR function

SET SERVEROUTPUT ON;
DECLARE
  ln_length       NUMBER;
  ln_block_size   NUMBER;
  lb_exist BOOLEAN := FALSE;
BEGIN
  UTL_FILE.fgetattr ('TESTORACLE',
                     'random.dat',
                     lb_exist,
                     ln_length,
                     ln_block_size);

  IF lb_exist
  THEN
    DBMS_OUTPUT.put_line ('FILE_EXISTS');
  ELSE
    DBMS_OUTPUT.put_line ('FILE NOT FOUND');
  END IF;
END;