Load CSV file in Oracle using PL/SQL

In one of my previous post I have created a CSV file using PL/SQL (Read Here). Here we are trying to load that CSV FILE in ORACLE normal table. Here we assume that the directory is created and the permissions are already granted also the CSV file has been exported.

To create a CSV file used in this article please read following post:
- http://nimishgarg.blogspot.in/2011/09/create-csv-file-using-plsql.html

To Create directory & and grant the read write permission to the specified USER, we need to execute following commands as SYS or SYSTEM user. e:\mycsv\ must be a physical path on the disk.

C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 3 15:07:01 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create or replace directory MYCSV as 'e:\mycsv\';
Directory created.

SQL> grant read, write on directory MYCSV to scott;
Grant succeeded.

To Load "EMP_DEPT.CSV" file in to Oracle Table we have created a table EMP_DEPT as

C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 29 15:47:05 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> CREATE TABLE EMP_DEPT
  2  (
  3  EMPNO NUMBER(4),
  4  ENAME VARCHAR2(10),
  5  SAL NUMBER(7,2),
  6  DNAME VARCHAR2(14)
  7  );

Table created.

Once the table is created, we just need to execute following pl/sql block to load the CSV file into the Oracle table.

SQL> DECLARE
  2    F UTL_FILE.FILE_TYPE;
  3    V_LINE VARCHAR2 (1000);
  4    V_EMPNO NUMBER(4);
  5    V_ENAME VARCHAR2(10);
  6    V_SAL NUMBER(7,2);
  7    V_DNAME VARCHAR2(14);
  8  BEGIN
  9    F := UTL_FILE.FOPEN ('MYCSV', 'EMP_DEPT.CSV', 'R');
10    IF UTL_FILE.IS_OPEN(F) THEN
11      LOOP
12        BEGIN
13          UTL_FILE.GET_LINE(F, V_LINE, 1000);
14          IF V_LINE IS NULL THEN
15            EXIT;
16          END IF;
17          V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
18          V_ENAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
19          V_SAL := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
20          V_DNAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
21          INSERT INTO EMP_DEPT VALUES(V_EMPNO, V_ENAME, V_SAL, V_DNAME);
22          COMMIT;
23        EXCEPTION
24        WHEN NO_DATA_FOUND THEN
25          EXIT;
26        END;
27      END LOOP;
28    END IF;
29    UTL_FILE.FCLOSE(F);
30  END;
31  /

PL/SQL procedure successfully completed.

Data of "EMP_DEPT" can be determind as following

SQL> SELECT * FROM EMP_DEPT;

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7369 SMITH            1000 20
      7499 ALLEN            2000 30
      7521 WARD             4000 30
      7566 JONES            2975 20
      7654 MARTIN           1250 30
      7698 BLAKE            2850 30
      7782 CLARK            2450 10
      7788 SCOTT            3000 20
      7839 KING             5000 10
      7844 TURNER           1500 30
      7876 ADAMS            1100 20
      7900 JAMES             950 30
      7902 FORD             3000 20
      7934 MILLER           1300 10

14 rows selected.

Other simple (and better) method might be to load CSV file in External table (Read here) and then simply execute a "insert into select" query.

Related Posts:
- Generate XML data using SQL in Oracle Database
- Convert XML to Rows and Columns in Oracle
- Create CSV file using PL/SQL
- Create External Table from CSV File
- Create XML file using PL/SQL
- Load XML File in Oracle Table
- Generate XML Output from SQL