Showing posts from June, 2017

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_exist