create or replace package File_To_Table_Pkg is

  -- Author  : JJM 
  -- Created : 1/21/2004 9:47:35 AM
  -- Purpose : Reads flat files on host and shifts delimited fields 
  --           into pre-defined tables
  
  PROCEDURE ld_paths_and_tables;

end File_To_Table_Pkg;
/
create or replace package body File_To_Table_Pkg IS

/***************************************************************************  
 This package can load any delimited text file from the host filesystem by
 making an entry in the paths_and_tables collection in the following format:
  
 'PATH|FILENAME|DELIM (numeric)|TABLENAME'
  
 PATH and FILENAME describe the Oracle instance filesystem. PATH must be
 set in ora.init UTIL_FILE_PATH parameter.
  
 DELIM is what ever integer the delimiter character bit pattern would form.
 This is done to accomodate printable (CSV files) and non-printable (TAB)
 delimiters.  All that's needed to stage extract files using this package is
 to create a table with the same number of VARCHAR2 fields as the extract file
 and then define an entry in the paths_and_tables collection for it.
 ****************************************************************************/

 -- map of extract files to tables
 TYPE vcarray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
 paths_and_tables       vcarray;

 --------------------------------------------------------------------------------
 -- insert_values
 -- parse delimited text and insert the resulting string fields into a table.
 -------------------------------------------------------------------------------- 
 PROCEDURE insert_values(p_line IN VARCHAR2, p_delim IN NUMBER, p_tablename IN VARCHAR2) IS
  
 l_len    NUMBER  DEFAULT 0;  
 l_ch     CHAR(1);             
 l_word   VARCHAR2(256);
 l_stmt   VARCHAR2(3000);
 l_values VARCHAR2(2500); 
   
 BEGIN 
    
  l_len := LENGTH(p_line) + 1;
  FOR i IN 1 .. l_len LOOP
     l_ch := substr(p_line,i,1);   
     -- process deliminters    
     IF (ascii(l_ch) = p_delim OR i = l_len) THEN
       l_values := l_values || '''' || l_word || '''' || ',';
       l_word := '';
     -- process data    
     ELSE 
         IF (ascii(l_ch) = 39) THEN -- single quote character
           l_word := l_word || '''' || '''';
         ELSE
           l_word := l_word || l_ch;
         END IF;
     END IF;   
  END LOOP;  
     
  IF (LENGTH(l_values) > 0) THEN 
    l_values := rtrim(l_values,',');
    l_stmt := 'INSERT INTO ' || p_tablename || ' VALUES (' || l_values || ')';
    EXECUTE IMMEDIATE l_stmt; 
  END IF;
     
  EXCEPTION
   WHEN OTHERS THEN 
    NULL;  -- TO DO:  Add appropriate error handling
    
 END insert_values;  
 
 --------------------------------------------------------------------------------
 -- read_file
 -- open a file and insert values from it one line at a time
 -------------------------------------------------------------------------------- 
 PROCEDURE read_file(p_path IN VARCHAR2, p_filename IN VARCHAR2, 
                     p_delim IN NUMBER, p_tablename IN VARCHAR2) IS
 
 l_file         utl_file.file_type;
 l_line         VARCHAR2(2000);
 l_counter      NUMBER DEFAULT 0;
 l_commit_rate  NUMBER DEFAULT 1000; 
 
 BEGIN
  
  l_file := utl_file.fopen(p_path, p_filename, 'r');
  LOOP
      utl_file.get_line(l_file, l_line);
      insert_values(l_line, p_delim, p_tablename);
      l_counter := l_counter + 1;
      
      IF (MOD(l_counter, l_commit_rate ) = 0) THEN
        COMMIT;
      END IF;
  END LOOP;
     
  EXCEPTION
   WHEN no_data_found THEN 
     COMMIT; 
     utl_file.fclose(l_file);                    
 END read_file;         
 
 --------------------------------------------------------------------------------
 -- ld_paths_and_tables
 -- parses positionally dependent fields in paths_and_tables collection and 
 -- calls read_file for each entry. The destination table is first deleted.
 --------------------------------------------------------------------------------  
 PROCEDURE ld_paths_and_tables IS
   
 l_path       VARCHAR2(256);
 l_filename   VARCHAR2(256);
 l_delim      NUMBER;
 l_tablename  VARCHAR2(256);
 l_ch         CHAR(1);
 l_word       VARCHAR2(256);
 l_len        NUMBER DEFAULT 0;
 k            NUMBER DEFAULT 1;
 
 BEGIN 
  
  FOR i IN 1 .. paths_and_tables.COUNT LOOP
       
     l_len := LENGTH(paths_and_tables(i)) + 1;
     FOR j IN 1 .. l_len LOOP  
       l_ch := substr(paths_and_tables(i),j,1); 
       IF (l_ch = '|' OR j = l_len) THEN  
               
          IF (k = 1) THEN
            l_path := l_word;             
          END IF;  
          IF (k = 2) THEN
            l_filename := l_word;
          END IF;
          IF (k = 3) THEN
            l_delim := l_word;
          END IF;          
          IF (k = 4) THEN
           l_tablename := l_word;
          END IF;  
         
          k := k + 1;
          l_word := '';
       ELSE
          l_word := l_word || l_ch;
       END IF;   
     END LOOP;
    
     IF (K <> 5) THEN
      dbms_output.put_line ('Invalid PATHS_AND_TABLES format');
     ELSE
       BEGIN 
         EXECUTE IMMEDIATE 'DELETE FROM ' || l_tablename;
         COMMIT;
         read_file(l_path, l_filename, l_delim, l_tablename);
 
         EXCEPTION
          WHEN NO_DATA_FOUND THEN
            NULL;
          WHEN OTHERS THEN 
            dbms_output.put_line('ld_paths_and_tables ' || SQLERRM);        
        END; 
     END IF;
     k := 1;
              
  END LOOP;
      
 END ld_paths_and_tables;
    
BEGIN
 -- init paths_and_tables map with extract files     
 paths_and_tables(1) := '/home/eucftp|dashboard_inventory2.dat|9|EUC.dp_inv_master_stg';
 paths_and_tables(1) := '/home/eucftp|dashboard_gl.dat|9|EUC.dp_inv_gl_stg';
 paths_and_tables(2) := '/home/eucftp|dashboard_prog.dat|9|EUC.dp_inv_prg_stg';

END File_To_Table_Pkg;
/