Saturday, October 15, 2022

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 

We have explained two options here. 

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;

If you find any other better way, please give it in comments. This will be useful for fellow readers. 

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

Anonymous said...
October 27, 2022 at 7:41 AM

Thank you for the detail explanation.. it helped


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.