PROCEDURE utlload 
--============================================================================
-- Copywright 2002, joel crainshaw & chet  west
--============================================================================
-- NAME:       utlload
-- PURPOSE:    generic loader code to loadd delimeted text into tables
--             in a similar manner as SQLLLOADER
--
-- NOTES:
--          minimum requirements are:
--            * properly configured utl_fiile dir
--            * delimeted file or fixed wiidth
--            * tablename to load into
--
--          PARAMETERS and other stuff
--			  p_table = table name to insert intoo
--			  p_filename = delimeted source file  to load from
--			  p_delimeter = delimeter; default =  ","; entering
--                                      deelimeter of FIX assumes fixed width columns
--                        p_cols = string  of columns to load in the order they
--                                 appear  in the text file;  separate each col
--                                 by any  standard delimeter (COMMA,SEMI,FWDSLASH,
--                                 BACKSLAASH, or PERIOD);  in absence of this
--                                 param,  code reads USER_TAB_COLUMNS to get the
--                                 columnss and ordering;  also, if a " is found in the
--                                 1st possition of any col, that col is assumed
--                                 to be qquoted (ie. "hello",world);
--                                 
--                                 
--                                 if p_deelimeter = FIX then format for column entry
--                                 is coluumnname#length,colname#length,...
--                                 (ex. naame#20,address#50,)
--                        p_commitrows = ccommit after each x rows processed defaults to 100
--			  p_where = optional param to identiffy the rows to be
--                                  insertted based on a column value in the source file;
--                                  ex: naame = BILL and amount > 100
--
--          errors
--            doesnt really handle errors  other than what oracle
--            kicks out
--
--          future enhancements???
--            better error handling, inputt, masking, control file,
--            output dir for results, etc.... all similar to sqlloader
--            not so obvious:  nested quotting (ie. """hello""","there"
--            will not properly load as thhis code doesnt look for nests)
--
--
-- MODIFICATION HISTORY
-- PERSON       DATE       COMMENTS
-- ----------   ---------- -------------------------------------------
-- joel         7/22/02    initial developpment
-- joel	        7/26/02    handle multiplee columns in where clause
--                         and fixed widthh column files
--============================================================================

                                          (
   p_table IN VARCHAR2
  ,p_filename IN VARCHAR2
  ,p_delimeter IN VARCHAR2 DEFAULT ','
  ,p_cols IN VARCHAR2 DEFAULT NULL
  ,p_commitrows IN INTEGER DEFAULT 100
  ,p_where IN VARCHAR2 DEFAULT NULL
) IS
   -----FILE HANDLING STUFF-----
   v_filehandle UTL_FILE.file_type;
   v_filedir VARCHAR2 (2000);
   v_filename VARCHAR2 (80);
   -----
   v_intval BINARY_INTEGER;
   v_strval VARCHAR2 (256);
   v_partyp BINARY_INTEGER;
      ----- PROCESSING STUFF -----
   --
   -- convert tabs and CRs to spaces in where clause
   --
   v_where VARCHAR2 (400)
      :=    LTRIM (
               RTRIM (
                  REPLACE (
                     TRANSLATE (p_where, CHR (32) || CHR (10) || CHR (9), '~~~')
                    ,'~'
                    ,CHR (32)
                  )
               )
            )
         || CHR (32);
   v_wherebuf VARCHAR2 (400);
   v_wherecnt INTEGER := 0;

   TYPE t_wherecols IS RECORD (
      v_wherecol VARCHAR2 (100)
     ,v_whereopr VARCHAR2 (20)
     ,v_whereval VARCHAR2 (400)
     ,v_wheresqlopr VARCHAR2 (20)
     ,v_wherepos INTEGER
   );

   TYPE pltab_wherecols IS TABLE OF t_wherecols
      INDEX BY BINARY_INTEGER;

   r_wherecols pltab_wherecols;
   -----
   v_colcnt INTEGER := 0;
   v_colbuf VARCHAR2 (4000);
   v_col VARCHAR2 (200);

   TYPE t_fixedlencol IS TABLE OF INTEGER
      INDEX BY BINARY_INTEGER;

   v_flen t_fixedlencol;
   v_fixed CHAR (3) := UPPER (SUBSTR (p_delimeter, 1, 3));
   -----
   v_insok BOOLEAN := TRUE ;
   v_insrows INTEGER := 0;
   v_linebuf VARCHAR2 (32000);
   v_sublen INTEGER := 0;
   v_subbuf VARCHAR2 (32000);
   v_sqt INTEGER := 0;
   v_eqt INTEGER := 0;
   -----
   dqt CHAR := '"';
   sqt CHAR := '''';
   com CHAR := ',';
   lpar CHAR := '(';
   rpar CHAR := ')';
   sp CHAR := ' ';
   ----- DYNAMIC SQL STUFF -----
   cursid INTEGER;
   cexec INTEGER;
   v_stmt1 VARCHAR2 (16000);
   v_stmt2 VARCHAR2 (16000);

   CURSOR c_tabcols IS
      SELECT   column_name
          FROM USER_TAB_COLUMNS
         WHERE table_name = p_table
      ORDER BY column_id;

---------------
   PROCEDURE closefile IS
   BEGIN
      UTL_FILE.fclose (v_filehandle);
   END;
---------------

BEGIN
   
------------------------------------
---------- GET UTL_FILE DIR INFO
------------------------------------
   BEGIN
      v_partyp := DBMS_UTILITY.get_parameter_value (
                     'utl_file_dir'
                    ,v_intval
                    ,v_strval
                  );

      IF v_strval IS NOT NULL THEN
         v_filedir := LTRIM (RTRIM (v_strval));
      ELSE
         v_filedir := '.\';
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         v_filedir := '.\';
   END;

   
----------------------------------------
----- open file and start process
----------------------------------------
   v_filename := NVL (p_filename, v_filename);
   v_filehandle := UTL_FILE.fopen (v_filedir, v_filename, 'r', 32767);

   
----------------------------------------
----- parse where clause
-----  format MUST BE
-----  COLUMN oper VALUE sqlopr
-----  ex:  amount = 10 and
-----       amount > 99
-----  (supports AND sqlopr only; sqlopr
-----   is ignored and AND always used)
----------------------------------------

   -----
   ----- strip multiple spaces from where clause
   -----
   LOOP
      EXIT WHEN INSTR (v_where, sp || sp) = 0;
      v_where := REPLACE (v_where, sp || sp, sp);
   END LOOP;

   r_wherecols.DELETE;
   r_wherecols (v_wherecnt + 1) := NULL;

   IF v_where IS NOT NULL THEN
      v_wherecnt := 1;

      FOR i IN 1 .. LENGTH (v_where) LOOP
         v_wherebuf := v_wherebuf || SUBSTR (v_where || sp, i, 1);

         IF     SUBSTR (v_where, i, 1) = sp
            AND LTRIM (v_wherebuf) IS NOT NULL THEN
            IF r_wherecols (v_wherecnt).v_wherecol IS NULL THEN
               r_wherecols (v_wherecnt).v_wherecol :=
                                                   LTRIM (RTRIM (v_wherebuf));
            ELSIF r_wherecols (v_wherecnt).v_whereopr IS NULL THEN
               r_wherecols (v_wherecnt).v_whereopr :=
                                                   LTRIM (RTRIM (v_wherebuf));
            ELSIF r_wherecols (v_wherecnt).v_whereval IS NULL THEN
               r_wherecols (v_wherecnt).v_whereval :=
                                                   LTRIM (RTRIM (v_wherebuf));
            ELSIF r_wherecols (v_wherecnt).v_wheresqlopr IS NULL THEN
               r_wherecols (v_wherecnt).v_wheresqlopr :=
                                                   LTRIM (RTRIM (v_wherebuf));
               v_wherecnt := v_wherecnt + 1;
               r_wherecols (v_wherecnt) := NULL;
            END IF;

            v_wherebuf := NULL;
         END IF;
      END LOOP;
   END IF;

   
----------------------------------------
----- parse col names
----------------------------------------
   v_stmt1 := 'insert into ' || p_table || lpar;
   v_colcnt := 1;

   IF p_cols IS NOT NULL THEN
      -----
      ----- use this if cols are passed in
      -----
      FOR i IN 1 .. LENGTH (p_cols) + 1 LOOP
         IF    SUBSTR (p_cols || sp, i, 1) IN (',', ';', '/', '\', '.')
            OR i = LENGTH (p_cols) + 1 THEN
            IF LTRIM (v_colbuf) IS NOT NULL THEN
               v_col := LTRIM (
                           RTRIM (
                              REPLACE (
                                 TRANSLATE (
                                    LTRIM (RTRIM (v_colbuf))
                                   ,',;/\.'
                                   ,'~~~~~'
                                 )
                                ,'~'
                                ,sp
                              )
                           )
                        );

               IF v_fixed = 'FIX' THEN
                  v_flen (v_colcnt) := SUBSTR (v_col, INSTR (v_col, '#') + 1);
                  v_col := SUBSTR (v_col, 1, INSTR (v_col, '#') - 1);
               END IF;

               -----
               ----- store the where clause position
               -----
               FOR j IN 1 .. v_wherecnt LOOP
                  IF v_col = r_wherecols (j).v_wherecol THEN
                     r_wherecols (j).v_wherepos := v_colcnt;
                  END IF;
               END LOOP;

               -----
               ----- add this col to the insert command
               -----
               v_stmt1 := v_stmt1 || v_col || com;
               v_colcnt := v_colcnt + 1;
            END IF;

            v_colbuf := NULL;
         END IF;

         v_colbuf := v_colbuf || SUBSTR (p_cols || sp, i, 1);
      END LOOP;
   ELSE
      -----
      ----- use this if cols not passed in
      -----
      FOR i IN c_tabcols LOOP
         -----
         ----- store the where clause position
         -----
         FOR j IN 1 .. v_wherecnt LOOP
            IF i.column_name = r_wherecols (j).v_wherecol THEN
               r_wherecols (j).v_wherepos := i.column_name;
            END IF;
         END LOOP;

         -----
         ----- add this col to the insert command
         -----
         v_stmt1 := v_stmt1 || i.column_name || com;
         v_colcnt := v_colcnt + 1;
      END LOOP;
   END IF;

   v_stmt1 := SUBSTR (v_stmt1, 1, LENGTH (v_stmt1) - 1);
   v_stmt1 := v_stmt1 || rpar || ' values ' || lpar;
   cursid := DBMS_SQL.open_cursor;

   
---------------------------------------
----- process input file
---------------------------------------
   LOOP
      BEGIN
         UTL_FILE.get_line (v_filehandle, v_linebuf);
         v_sublen := 0;
         v_insok := TRUE ;

         FOR i IN 1 .. v_colcnt - 1 LOOP
            v_linebuf := LTRIM (v_linebuf);

            IF NVL (v_fixed, 'x') != 'FIX' THEN
               IF SUBSTR (v_linebuf, 1, 1) = dqt THEN
                  v_sqt := INSTR (v_linebuf, dqt, 1, 1);
                  v_eqt := INSTR (v_linebuf, dqt, v_sqt + 1, 1);
                  v_sublen := INSTR (v_linebuf, p_delimeter, v_eqt, 1);
                  v_subbuf := SUBSTR (v_linebuf, v_sqt + 1, v_eqt - 2);
               ELSE
                  v_sublen := INSTR (v_linebuf, p_delimeter, 1, 1);

                  IF v_sublen < 1 THEN
                     v_sublen := 32000;
                  END IF;

                  v_subbuf := SUBSTR (v_linebuf, 1, v_sublen - 1);
               END IF;
            ELSIF v_fixed = 'FIX' THEN
               v_subbuf := SUBSTR (v_linebuf, 1, v_flen (i));
            END IF;

            FOR j IN 1 .. v_wherecnt LOOP
               IF     (i = r_wherecols (j).v_wherepos)
                  AND (   (    r_wherecols (j).v_whereopr IN ('=')
                           AND r_wherecols (j).v_whereval <> v_subbuf
                          )
                       OR (    r_wherecols (j).v_whereopr IN ('!=', '<>')
                           AND r_wherecols (j).v_whereval = v_subbuf
                          )
                       OR (    r_wherecols (j).v_whereopr IN ('<')
                           AND r_wherecols (j).v_whereval >= v_subbuf
                          )
                       OR (    r_wherecols (j).v_whereopr IN ('>')
                           AND r_wherecols (j).v_whereval <= v_subbuf
                          )
                       OR (    r_wherecols (j).v_whereopr IN ('<=')
                           AND r_wherecols (j).v_whereval > v_subbuf
                          )
                       OR (    r_wherecols (j).v_whereopr IN ('>=')
                           AND r_wherecols (j).v_whereval < v_subbuf
                          )
                      ) THEN
                  v_insok := FALSE ;
                  EXIT;
               END IF;
            END LOOP;

            IF v_insok = FALSE THEN
               EXIT;
            ELSE
               v_stmt2 :=    v_stmt2
                          || sqt
                          || REPLACE (v_subbuf, sqt, sqt || sqt)
                          || sqt
                          || com;
               v_linebuf := SUBSTR (v_linebuf, v_sublen + 1);
            END IF;
         END LOOP;

         -----
         ----- insert the text
         -----
         IF v_insok = TRUE THEN
            v_stmt2 := SUBSTR (v_stmt2, 1, LENGTH (v_stmt2) - 1) || rpar;
            DBMS_SQL.Parse (cursid, v_stmt1 || v_stmt2, DBMS_SQL.native);
            cexec := DBMS_SQL.EXECUTE (cursid);
            v_insrows := v_insrows + 1;

            IF v_insrows >= p_commitrows THEN
               COMMIT;
               v_insrows := 0;
            END IF;
         END IF;

         v_stmt2 := NULL;
      EXCEPTION
         WHEN UTL_FILE.read_error THEN
            DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
            EXIT;
         WHEN VALUE_ERROR THEN
            DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
            EXIT;
         WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line ('EOF');
            EXIT;
         WHEN UTL_FILE.invalid_path THEN
            DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
            EXIT;
         WHEN UTL_FILE.invalid_filehandle THEN
            DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
            EXIT;
         WHEN UTL_FILE.invalid_mode THEN
            DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
            EXIT;
         WHEN UTL_FILE.invalid_operation THEN
            DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
            EXIT;
         WHEN UTL_FILE.internal_error THEN
            DBMS_OUTPUT.put_line (SQLERRM (SQLCODE));
      END;
   END LOOP;

   DBMS_SQL.close_cursor (cursid);
   closefile;
   COMMIT;
END;
--__--__--__--__--__--__--__--__--__--__---__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__---__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__---__--__--__--__--__--__--__--__--__