How to Check If File Exists in PL/SQL?

How to Check If File Exists in PL/SQL?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 1 min read

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.

See also: