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;

/