Check if file exists in Oracle Directory

Solution:

Step 1: Click Oracle Directories to understand about directory creation.

Step 2: Move BLOB files into Oracle Directory.

Step 3: Create a PLSQL function file_exists, which will help us to check whether the given file exists in the given directory or not.

CREATE OR REPLACE
  FUNCTION file_exists(
      p_dirname  IN VARCHAR2, -- Put your oracle directory name
      p_filename IN VARCHAR2 )
    RETURN BOOLEAN
  IS
    l_file_loc BFILE;
    l_exists NUMBER;
  BEGIN
    l_file_loc := bfilename(upper(p_dirname), p_filename);
    l_exists   := dbms_lob.fileexists(l_file_loc); -- 1 exists; 0 - not exists
   
    IF l_exists = 1 THEN
      RETURN TRUE;
    elsif l_exists = 0 THEN
      RETURN FALSE;
    END IF;
    
  EXCEPTION
  WHEN utl_file.invalid_path THEN
    RETURN FALSE;
  WHEN utl_file.invalid_operation THEN
    RETURN FALSE;
  END file_exists;

Step 2: Call the function and test it.

SET serveroutput ON;

BEGIN
  IF ( file_exists( 'TEMP', 'samplereport.pdf' )) THEN
    dbms_output.put_line( 'File Exists.' );
  ELSE
    dbms_output.put_line( 'File Not Exists.' );
  END IF;
END;

Output:

Related Posts:

• Oracle Directories
• Move BLOB files into Oracle Directory