How to read a CSV file using PLSQL and loading into a table

A simple example demonstrating how you can quickly read a csv file and load data into a table.

STEP:1 create directory object in DB

create directory EMPDATA as '/home/oracle/EMPDATA';

STEP:2 Create a table to which csv data will be inserted 

  CREATE TABLE EMP_DEPT

    (

    EMPNO NUMBER(4),

    ENAME VARCHAR2(10),

    SAL NUMBER(7,2),

    DNAME VARCHAR2(14)

    );  

STEP:3  Run the below procedure to read data from CSV and insert into table emp_dept

DECLARE

      F UTL_FILE.FILE_TYPE;

      V_LINE VARCHAR2 (1000);

      V_EMPNO NUMBER(4);

      V_ENAME VARCHAR2(10);

      V_SAL NUMBER(7,2);

      V_DNAME VARCHAR2(14);

    BEGIN

     F := UTL_FILE.FOPEN ('EMPDATA', 'employee.csv', 'R');

    IF UTL_FILE.IS_OPEN(F) THEN

      LOOP

        BEGIN

          UTL_FILE.GET_LINE(F, V_LINE, 1000);

          IF V_LINE IS NULL THEN

            EXIT;

          END IF;

          V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);

          V_SAL := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);

          V_ENAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);

          V_DNAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);

          INSERT INTO EMP_DEPT VALUES(V_EMPNO, V_ENAME, V_SAL, V_DNAME);

          COMMIT;

        EXCEPTION

        WHEN NO_DATA_FOUND THEN

          EXIT;

        END;

      END LOOP;

    END IF;

    UTL_FILE.FCLOSE(F);

  END;

  /

STEP:4 Select the table emp_dept and check if csv data are loaded or not

SQL> Select * from emp_dept;

SQL> select * from emp_dept;

     EMPNO ENAME             SAL DNAME

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

         1 manoj            1000 IT

         2 Santanu          2000 Accounting

         3 Manjit           3000 Billing

I hope this can help you. 

Reference: http://nimishgarg.blogspot.in/2013/04/load-csv-file-in-oracle-using-plsql.html