DB Procedure to check if file exists

DB Procedure to check if file exists

This script is required when you want to check a file if it physically exists on the server or not. Use to two out parameters when you register this procedure a concurrent program otherwise comment them out. This procedure uses concept of exception handling to return the value when you try to open the file.
When you register it as a concurrent program pass two parameters to the concurrent program as follows.
1. Directory
2. File Name
Concurrent program will comple with status as "Normal" when file exists and as "Warning" when doesn't exist.
Code:
CREATE OR REPLACE PROCEDURE apps.erp_schools_checkfile (
errbuf OUT VARCHAR2, -- This parameter is required only when this procedure is registered as a concurrent program
retcode OUT NUMBER, -- This parameter is required only when this procedure is registered as a concurrent program
p_directory IN VARCHAR2,
p_filename IN VARCHAR2
)
IS
v_file SYS.UTL_FILE.file_type;
v_filename1 VARCHAR2 (100) := NULL;
v_output_dir VARCHAR2 (200) := NULL;
code VARCHAR2 (2000) := NULL;
error VARCHAR2 (2000) := NULL;
BEGIN
v_filename1 := p_filename;
fnd_file.put_line (fnd_file.LOG, 'FILENAME = ' v_filename1);
v_output_dir := p_directory;
fnd_file.put_line (fnd_file.LOG, 'DIRECTORY = ' v_output_dir);
v_file := UTL_FILE.fopen (v_output_dir, v_filename1, 'r');
UTL_FILE.fclose (v_file);
retcode := 0;
errbuf := 'FILE OPENED AND CLOSED SUCCESSFULLY';
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID PATH (' v_output_dir);
retcode := 1;
errbuf := 'INVALID PATH ';
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line (fnd_file.LOG, 'INTERNAL ERROR ');
retcode := 1;
errbuf := 'INTERNAL ERROR ';
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID FILEHANDLE ');
retcode := 1;
errbuf := 'INVALID FILEHANDLE ';
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID MODE ');
retcode := 1;
errbuf := 'INVALID MODE ';
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line (fnd_file.LOG, 'INVALID OPERATION ');
retcode := 1;
errbuf := 'INVALID OPERATION ';
WHEN UTL_FILE.read_error
THEN
fnd_file.put_line (fnd_file.LOG, 'FILE READ ERROR ');
retcode := 1;
errbuf := 'FILE READ ERROR ';
WHEN OTHERS
THEN
error := SQLERRM;
fnd_file.put_line (fnd_file.LOG, 'THE ERROR IS ' error);
retcode := 1;
errbuf := 'THE ERROR IS ' error;
END erp_schools_checkfile;
/