CREATE TABLE TEMP
(
  COL1  NUMBER,
  COL2  VARCHAR2(40 BYTE),
  COL3  DATE
)
/
Data

COL1	COL2	COL3
1,AA,1-Jan-13
2,BB,1-Jan-13
3,CC,1-Jan-13


CREATE OR REPLACE PROCEDURE load_csv (
   p_filename             IN VARCHAR2,
   p_directory            IN VARCHAR2 DEFAULT 'ORA_DIR',
   p_ignore_headerlines   IN INTEGER DEFAULT 1,
   p_delimiter            IN VARCHAR2 DEFAULT ',')
IS
   v_filehandle       UTL_FILE.file_type;
   v_text             VARCHAR2 (32767);
   v_eof              BOOLEAN := FALSE;
   v_fields           DBMS_SQL.varchar2a;
   v_field_index      INTEGER;
   v_length           INTEGER;
   v_start            INTEGER;
   v_index            INTEGER;
   v_enclosed_start   INTEGER;
   v_enclosed_end     INTEGER;
BEGIN
   v_filehandle := UTL_FILE.fopen (p_directory,p_filename,'r',32767);

   IF p_ignore_headerlines > 0
   THEN
      BEGIN
         FOR i IN 1 .. p_ignore_headerlines
         LOOP
            UTL_FILE.get_line (v_filehandle, v_text);
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_eof := TRUE;
      END;
   END IF;

   WHILE NOT v_eof
   LOOP
      BEGIN
            UTL_FILE.get_line (v_filehandle, v_text);
            v_fields.delete;
  v_field_index := 0;
  v_length := LENGTH (v_text);
  v_start := 1;
   v_enclosed_start := INSTR (v_text, '"', 1);
   v_enclosed_end := INSTR (v_text, '"', v_enclosed_start + 1);
  WHILE (v_start <= v_length)
        LOOP
            v_index := INSTR (v_text, p_delimiter, v_start);
            IF     v_enclosed_end != 0
               AND v_index > v_enclosed_start
               AND v_index < v_enclosed_end
            THEN
               v_index := INSTR (v_text, p_delimiter, v_enclosed_end);
               v_enclosed_start := INSTR (v_text, '"', v_enclosed_end + 1);

               IF v_enclosed_start != 0
               THEN
                  v_enclosed_end := INSTR (v_text, '"', v_enclosed_start + 1);
               END IF;
            END IF;

            IF v_index = 0
            THEN
               v_fields (v_field_index) :=
                  TRIM (LTRIM (RTRIM (SUBSTR (v_text, v_start), '"'), '"'));
               v_start := v_length + LENGTH (p_delimiter);
            ELSE
               v_fields (v_field_index) :=
                  TRIM (
                     LTRIM (
                        RTRIM (SUBSTR (v_text, v_start, v_index - v_start),
                               '"'),
                        '"'));
               v_start := v_index + LENGTH (p_delimiter);
            END IF;

            v_field_index := v_field_index + 1;
         END LOOP;

         INSERT INTO temp (col1, col2, col3)
              VALUES (
                        v_fields (0),
                        v_fields (1),
                        TO_DATE (v_fields (3), 'MM/DD/YYYY'));
        Commit;
        EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_eof := TRUE;
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.
             put_line ('Stop' );
            
      END;
   END LOOP;

   UTL_FILE.fclose (v_filehandle);
END load_csv;

 DECLARE  

  P_FILENAME VARCHAR2(200):= 'DATA_PUMP_07_Mar_13.csv';  

  P_DIRECTORY VARCHAR2(200):= 'DUMPDIR1';  

  P_IGNORE_HEADERLINES NUMBER := 1;  

  BEGIN  

  LOAD_CSV( P_FILENAME, P_DIRECTORY, P_IGNORE_HEADERLINES);  
   END; 

CREATE OR REPLACE PROCEDURE load_csv (
   p_filename             IN VARCHAR2,
   p_directory            IN VARCHAR2 DEFAULT 'DUMPDIR1',
   p_ignore_headerlines   IN INTEGER DEFAULT 1,
   p_delimiter            IN VARCHAR2 DEFAULT ',')
IS
   v_filehandle       UTL_FILE.file_type;
   v_text             VARCHAR2 (32767);
   v_eof              BOOLEAN := FALSE;
   v_fields           DBMS_SQL.varchar2a;
   v_field_index      INTEGER;
   v_length           INTEGER;
   v_start            INTEGER;
   v_index            INTEGER;
   v_enclosed_start   INTEGER;
   v_enclosed_end     INTEGER;
BEGIN
   v_filehandle := UTL_FILE.FOPEN(p_directory,p_filename,'R',32767);

   IF 1 > 0
   THEN
      BEGIN
         FOR i IN 1 .. p_ignore_headerlines
         LOOP
            UTL_FILE.get_line (v_filehandle, v_text);
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_eof := TRUE;
      END;
   END IF;

   WHILE NOT v_eof
   LOOP
      BEGIN
            UTL_FILE.get_line (v_filehandle, v_text);
            v_fields.delete;
  v_field_index := 0;
  v_length := LENGTH (v_text);
  v_start := 1;
   v_enclosed_start := INSTR (v_text, '"', 1);
   v_enclosed_end := INSTR (v_text, '"', v_enclosed_start + 1);
  WHILE (v_start <= v_length)
        LOOP
            v_index := INSTR (v_text, p_delimiter, v_start);
            IF     v_enclosed_end != 0
               AND v_index > v_enclosed_start
               AND v_index < v_enclosed_end
            THEN
               v_index := INSTR (v_text, p_delimiter, v_enclosed_end);
               v_enclosed_start := INSTR (v_text, '"', v_enclosed_end + 1);

               IF v_enclosed_start != 0
               THEN
                  v_enclosed_end := INSTR (v_text, '"', v_enclosed_start + 1);
               END IF;
            END IF;

            IF v_index = 0
            THEN
               v_fields (v_field_index) :=
                  TRIM (LTRIM (RTRIM (SUBSTR (v_text, v_start), '"'), '"'));
               v_start := v_length + LENGTH (p_delimiter);
            ELSE
               v_fields (v_field_index) :=
                  TRIM (
                     LTRIM (
                        RTRIM (SUBSTR (v_text, v_start, v_index - v_start),
                               '"'),
                        '"'));
               v_start := v_index + LENGTH (p_delimiter);
            END IF;

            v_field_index := v_field_index + 1;
         END LOOP;

         INSERT INTO temp (col1, col2, col3)
              VALUES (
                        v_fields (0),
                        v_fields (1),
                        TO_DATE (v_fields (3),'DD-Mon-YY'));
        Commit;
        EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_eof := TRUE;
      END;
   END LOOP;

   UTL_FILE.fclose (v_filehandle);
END;


 DECLARE  

  P_FILENAME VARCHAR2(200):= 'DATA_PUMP_07_Mar_13.csv';  

  P_DIRECTORY VARCHAR2(200):= 'DUMPDIR1';  

  P_IGNORE_HEADERLINES NUMBER := 1;  

  BEGIN  

  LOAD_CSV( P_FILENAME, P_DIRECTORY, P_IGNORE_HEADERLINES);  
   END; 

SQL> CREATE OR REPLACE PROCEDURE load_csv (
  2   p_filename IN VARCHAR2,
  3   p_directory IN VARCHAR2 DEFAULT 'DUMPDIR1',
  4   p_ignore_headerlines IN INTEGER DEFAULT 1,
  5   p_delimiter IN VARCHAR2 DEFAULT ',')
  6  IS
  7  
  8   v_filehandle UTL_FILE.file_type;
  9   v_text VARCHAR2(32767);
 10   v_eof BOOLEAN := FALSE;
 11   v_fields DBMS_SQL.varchar2a;
 12   v_field_index INTEGER;
 13   v_length INTEGER;
 14   v_start INTEGER;
 15   v_index INTEGER;
 16   v_enclosed_start INTEGER;
 17   v_enclosed_end INTEGER;
 18  BEGIN
 19   v_filehandle := UTL_FILE.fopen(p_directory, p_filename, 'r', 3276
 20  
 21  IF p_ignore_headerlines > 0
 22   THEN
 23   BEGIN
 24   FOR i IN 1 .. p_ignore_headerlines
 25   LOOP
 26   UTL_FILE.get_line(v_filehandle, v_text);
 27   END LOOP;
 28   EXCEPTION
 29   WHEN NO_DATA_FOUND
 30   THEN
 31   v_eof := TRUE;
 32   END;
 33   END IF;
 34  
 35  WHILE NOT v_eof
 36   LOOP
 37   BEGIN
 38   DBMS_OUTPUT.put_line(
 39   '----------------------------------------------------------------
 40   UTL_FILE.get_line(v_filehandle, v_text);
 41   DBMS_OUTPUT.put_line('v_text=' || v_text);
 42   v_fields.delete;
 43   v_field_index := 0;
 44   v_length := LENGTH(v_text);
 45   v_start := 1;
 46   v_enclosed_start := INSTR(v_text, '"', 1);
 47   v_enclosed_end := INSTR(v_text, '"', v_enclosed_start + 1);
 48  
 49  WHILE (v_start <= v_length)
 50   LOOP
 51   v_index := INSTR(v_text, p_delimiter, v_start);
 52  
 53  IF v_enclosed_end != 0
 54   AND v_index > v_enclosed_start
 55   AND v_index < v_enclosed_end
 56   THEN
 57   v_index := INSTR(v_text, p_delimiter, v_enclosed_end);
 58   v_enclosed_start := INSTR(v_text, '"', v_enclosed_end + 1);
 59  
 60  IF v_enclosed_start != 0
 61   THEN
 62   v_enclosed_end :=
 63   INSTR(v_text, '"', v_enclosed_start + 1);
 64   END IF;
 65   END IF;
 66  
 67  IF v_index = 0
 68   THEN
 69   v_fields(v_field_index) :=
 70   TRIM(LTRIM(RTRIM(SUBSTR(v_text, v_start), '"'), '"'));
 71   v_start := v_length + LENGTH(p_delimiter);
 72   ELSE
 73   v_fields(v_field_index) :=
 74   TRIM(
 75   LTRIM(
 76   RTRIM(
 77   SUBSTR(v_text, v_start, v_index - v_start),
 78   '"'),
 79   '"'));
 80   v_start := v_index + LENGTH(p_delimiter);
 81   END IF;
 82  
 83  v_field_index := v_field_index + 1;
 84   END LOOP;
 85   INSERT
 86   INTO temp(col1,col2, col3)
 87   VALUES (
 88   v_fields(0),
 89   v_fields(1),
 90   TO_DATE(v_fields(2), 'DD-Mon-YY'));
 91  commit;
 92  
 93   EXCEPTION
 94   WHEN NO_DATA_FOUND
 95   THEN
 96   v_eof := TRUE;
 97   
 98   END;
 99   END LOOP;
100  
101  UTL_FILE.fclose(v_filehandle);
102   END load_csv;
103  
104  /

Procedure created.

SQL>  DECLARE  
  2  
  3    P_FILENAME VARCHAR2(200):= 'tmp.csv';  
  4  
  5    P_DIRECTORY VARCHAR2(200):= 'DUMPDIR1';  
  6  
  7    P_IGNORE_HEADERLINES NUMBER := 1;  
  8  
  9    BEGIN  
 10  
 11    LOAD_CSV( P_FILENAME, P_DIRECTORY, P_IGNORE_HEADERLINES);  
 12     END ;
 13  /

PL/SQL procedure successfully completed.

SQL> select * from temp;

no rows selected

SQL>