To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.
CREATE FUNCTION
status (DIRECTORY_NAME VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF (DBMS_LOB.FILEEXISTS(
BFILENAME (DIRECTORY_NAME, '.')) = 1)
THEN
RETURN 'VALID';
ELSE
RETURN 'INVALID';
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN SQLERRM;
END;
/
SELECT
directory_name NAME,
directory_path PATH,
status (directory_name) STATUS
FROM dba_directories;
NAME PATH STATUS
---- ---- ---------
FOO /foo INVALID
TMP /tmp VALID
BAK /u99 VALID