The following is a stored function example to check if a file exists in PL/SQL.
PL/SQL Function Example To Check If a File Exists
The below function takes two parameters, one is for Oracle directory object name and the second one is the file name and returns the Boolean type. It will return true if a file exists else returns false.
CREATE OR REPLACE FUNCTION is_file_exist (p_directory IN VARCHAR2, p_filename IN VARCHAR2) RETURN BOOLEAN AS n_length NUMBER; n_block_size NUMBER; b_exist BOOLEAN := FALSE; BEGIN UTL_FILE.fgetattr (p_directory, p_filename, b_exist, n_length, n_block_size); RETURN b_exist; END is_file_exist;
Test
SET SERVEROUTPUT ON;
BEGIN
IF is_file_exist ('CSV_DIR', 'emp.dat')
THEN
DBMS_OUTPUT.put_line ('File exists.');
ELSE
DBMS_OUTPUT.put_line ('File not exists.');
END IF;
END;
/Output
File exists. PL/SQL procedure successfully completed.
