Load data from csv to oracle table using PLSQL

Load data from csv to oracle table using PLSQL

CREATE TABLE apps.xx_badlog
(
  errm  VARCHAR2(4000 BYTE),
  DATA  VARCHAR2(4000 BYTE)
);

----------------------------------------------------------

CREATE OR REPLACE FUNCTION apps.xx_load_data(
   p_table                    IN       VARCHAR2
 , p_dir                      IN       VARCHAR2
 , p_filename                 IN       VARCHAR2
 , p_delimiter                IN       VARCHAR2 DEFAULT '|'
)
   RETURN NUMBER
IS
   l_input           UTL_FILE.file_type;
   l_thecursor       INTEGER            DEFAULT DBMS_SQL.open_cursor;
   l_buffer          VARCHAR2(4000);
   l_lastline        VARCHAR2(4000);
   l_status          INTEGER;
   l_colcnt          NUMBER             DEFAULT 0;
   l_cnt             NUMBER             DEFAULT 0;
   l_sep             CHAR(1)            DEFAULT NULL;
   l_errmsg          VARCHAR2(4000);
   l_cnames          VARCHAR2(4000);
   l_cons_cname      VARCHAR(50);
   l_num             NUMBER;
   l_header_seq_id   NUMBER;
   l_rec_num         NUMBER             := 0;
   l_request_id      NUMBER             := fnd_global.conc_request_id;
   l_file_id         NUMBER             := 1;
BEGIN
   l_num                                     := 10;
   l_input                                   := UTL_FILE.fopen(p_dir
                                                             , p_filename
                                                             , 'r'
                                                              );
   l_num                                     := 20;
   l_buffer                                  := 'insert into ' || p_table || ' values ( ';
   l_num                                     := 30;

   FOR z IN (SELECT   column_name
                 FROM dba_tab_columns
                WHERE table_name = p_table
             ORDER BY column_id ASC)
   LOOP
      IF l_cnames IS NULL
      THEN
         l_cnames                                  := z.column_name;
      ELSE
         l_cnames                                  := l_cnames || ',' || z.column_name;
      END IF;
   END LOOP;

   l_num                                     := 40;

   BEGIN
      l_colcnt                                  := LENGTH(l_cnames) - LENGTH(REPLACE(l_cnames
                                                                                   , ','
                                                                                   , ''
                                                                                    )) + 1;
      l_num                                     := 45;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(l_num || ' Error ' || SQLERRM);
   END;

   l_num                                     := 50;

   FOR i IN 1 .. l_colcnt
   LOOP
      l_buffer                                  := l_buffer || l_sep || ':b' || i;
      l_sep                                     := ',';
   END LOOP;

   l_num                                     := 60;
   l_buffer                                  := l_buffer || ')';
   l_num                                     := 70;
   DBMS_SQL.parse(l_thecursor
                , l_buffer
                , DBMS_SQL.native
                 );
   l_num                                     := 80;

   LOOP
      BEGIN
         UTL_FILE.get_line(l_input, l_lastline);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

      SELECT xx_file_load_seq.NEXTVAL
        INTO l_header_seq_id                                                               -- Sequence For Header_seq_id
        FROM DUAL;

      l_rec_num                                 := l_rec_num + 1; --- variable for Source Record Number in the CSV file.
      l_num                                     := 90;
      l_buffer                                  :=
         l_header_seq_id || ',' || l_file_id || ',' || l_rec_num || ',' || l_request_id || ',' || l_lastline
         || p_delimiter;
      --      DBMS_OUTPUT.put_line('l_buffer ' || l_buffer);
      l_num                                     := 100;

      FOR i IN 1 .. l_colcnt
      LOOP
         DBMS_SQL.bind_variable(l_thecursor
                              , ':b' || i
                              , SUBSTR(l_buffer
                                     , 1
                                     , INSTR(l_buffer, p_delimiter) - 1
                                      )
                               );
         l_buffer                                  := SUBSTR(l_buffer, INSTR(l_buffer, p_delimiter) + 1);
         l_num                                     := 110;
      END LOOP;

      BEGIN
         l_status                                  := DBMS_SQL.EXECUTE(l_thecursor);
         l_cnt                                     := l_cnt + 1;
         l_num                                     := 120;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_errmsg                                  := SQLERRM;

            INSERT INTO xx_badlog
                        (errm
                       , DATA
                        )
                 VALUES (l_errmsg
                       , l_lastline
                        );
      END;
   END LOOP;

   DBMS_SQL.close_cursor(l_thecursor);
   UTL_FILE.fclose(l_input);
   l_num                                     := 130;
   COMMIT;
   RETURN l_cnt;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(l_num || ' Unexpected Error ' || SQLERRM);
      RETURN NULL;
END xx_load_data;