Understanding Utl_file

Question

So I am changing the procedure of this parse_file 'clean_files_up procedure'. I have coded the changes but after testing I realized I am actually having a hard time understanding what is going on. I have never worked with parsing a file or utl_file so I don't entirely undrestand the concept.

So in summary, we need it to look for a file called Files20130807.xxx with the current sysdate from a directory called NEWFILE_DIRECTORY. IF there is no file meaning that it can not find a file matching the current sysdate it needs to run the orignial file which is located in a different directory called ORIGINALFILE_DIRECTORY and then go the the end of the procedure. If there is a file that matches the current sysdate then it needs to continue through the procedure. When it gets to actually opening up the file it will open the file that matches the current sysdate from NEWFILE_DIRECTORY and then will parse the file, otherwise if the current file from NEWFILE_DIRECTORY is open then it will need to open the original file located in ORIGNIALFILE_DIRECTORY and then will parse that file instead.

PROCEDURE clean_files_up IS
v_fileName VARCHAR2(20) := Files || to_char(sysdate, 'YYYYMMDD') || '.xxx';
v_inFile  utl_file.file_type;
v_outFile  utl_file.file_type;
v_line      VARCHAR2(2000);
v_newLine  VARCHAR2(2000);
v_count     NUMBER := 1; 

BEGIN

--No Files found for current date 
  IF (substr(v_fileName, 6, 8)) <> to_char(sysdate, 'YYYYMMDD') THEN
   v_inFile := utl_file.fopen('ORIGINALFILE_DIRECTORY', 'OriginalFile.xxx', 'r');  

   RETURN;  --go to end of procedure
  END IF;

   v_inFile := utl_file.fopen ('NEWFILE_DIRECTORY', 'v_fileName', 'r');   
    IF utl_file.is_open(v_inFile) THEN
      v_outFile := utl_file.fopen('ORIGINALFILE_DIRECTORY',                      
                               'OriginalFile.xxx',
                               'W');

 LOOP
    BEGIN
      utl_file.get_line(v_inFile, v_line);
      IF v_line IS NULL THEN
        EXIT;
      END IF;
      IF v_count > 1 THEN
        get_new_csv_line(v_line, v_newLine);
        utl_file.put_line(v_outFile, v_newLine);
        utl_file.fflush(v_outFile);
      END IF;
      v_count := v_count + 1;
    EXCEPTION
      WHEN no_data_found THEN
      EXIT; 
    END;
  END LOOP;
  utl_file.fclose(v_outFile);
END IF;

 EXCEPTION
  WHEN OTHERS THEN
     Condition;

END clean_files_up;

Questions:

1. So I need to create a new directory called NEWFILE_DIRECTORY located in some file path /xxx/xxx/xxx that has the list of files like ; Files20130804.xxx, Files20130805.xxx, Files20130806.xxx, Files20130807 locted in it. I don't know where inside of this procedure I would have the new directory being created or even if I can create it within the procedure

CREATE OR REPLACE DIRECTORY newfile_directory in as '/xxx/xxx/xxx'

2.. I need to read in these files to make sure that it matches the sysdate otherwise it needs to run the original file and then go to the end of the proceudre. This part is the IF statement right after the BEGIN. I am just confussed in how it knows to look in the NEWFILE_DIRECTORY to find the matching file name Files20130807.xxx to see if it is a match or not.

3.. This is a similiar question to #2 but how does (after the first End IF;) it know where the file path for NEWFILE_DIRECOTRY and ORIGINALFILE_DIRECTORY is located without giving or defining a path ?

4.. Lastly when testing the code I noticed that after it reads in the v_inFile := utl_file.fopen ('NEWFILE_DIRECTORY', 'v_fileName', 'r'); (right after the first End IF;) it goes down to the exception without continuing through the if and to the loop.

I would really appreciate it someone could answer theses questions are at least help to understand what is going on. Also if I need to clearfiy and of the questions (1-4) I can do that.

Answer 1

Answers on your questions:

1) Following code creates link from Oracle internally defined directory (can see them by selecting data from DBA_DIRECTORIES) to file system directory.

CREATE OR REPLACE DIRECTORY NEWFILE_DIRECTORY AS '/xxx/xxx/xxx'

If path '/xxx/xxx/xxx' already exists in file system, then this will be correct way.

2) If I understand you correctly, then you must check that in NEWFILE_DIRECTORY exists file with current date in it's name. It can be done with following function:

FUNCTION check_if_file_exists
  (p_file_name IN VARCHAR2
  ,p_file_dir IN VARCHAR2)
RETURN BOOLEAN
IS
  v_file  utl_file.file_type;
BEGIN
  v_file := utl_file.fopen(p_file_dir, p_file_name, 'R');

  IF utl_file.is_open(v_file) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
EXCEPTION
  WHEN UTL_FILE.invalid_path THEN
    RETURN FALSE;
  WHEN utl_file.invalid_operation THEN
    RETURN FALSE;
END check_if_file_exists;

And your first if will be:

IF check_if_file_exists(v_fileName, 'NEWFILE_DIRECTORY') THEN

3) You can create DB directories (mapping for DB to find file system path) by "CREATE OR REPLACE DIRECTORY" (see #1)

4) You pass parameter as string value 'v_fileName' not variable v_fileName. Correct code would be:

v_inFile := utl_file.fopen ('NEWFILE_DIRECTORY', v_fileName, 'r');   

Answer 2

Janis Baiza thank you for the answer it really helped me to understand what was going on.

I actually found that this will check to see if the file exists in the current directory as well and if it does then outputs that it exists and if it doesn't then out put it doesn't exist and run the original file then return to end of procedure.

utl_file.fgetattr ('NEWFILE_DIRECTORY', v_fileName, v_check_fileEX, v_fLength, v_Bsize );
IF v_check_fileEX THEN
dbms_output.put_line('file exists');
END IF;
IF NOT v_check_fileEX THEN
dbms_output.put_line('file does not exist');

v_inFile := utl_file.fopen('ORIGINALFILE_DIRECTORY', 'OriginalFile.xxx', 'r');

 RETURN; 
END IF;