UTL_FILE Import Data into ORACLE TABLE

How to Import Data from flat files into ORACLE Table?

As we have already seen How we can Export Data from ORACLE Table to flat files in our previous post. Click here to know How To Export Data From Table to Flat Files.

We will follow the same steps for IMPORT as well.

•          Create a Directory Object from where you will read file.
•          Create Stored Procedure with UTL_FILE Package
•          Now run the Procedure and check if Data has been Imported

1. Create a Directory Object from where you will read file.

Note that before using UTL_FILE package in Oracle you may need to enable it and create a directory object:

CONNECT SYS AS SYSDBA;

***Enable UTL_FILE****

  GRANT EXECUTE ON UTL_FILE TO PUBLIC;

****Create a directory object for UTL_FILE and grant permissions****

  CREATE DIRECTORY IMPORTDIR AS 'F:\IMPORT';

  GRANT READ. WRITE ON DIRECTORY IMPORTDIR TO PUBLIC;

NOTE: You will need to create Directory manually and can set the target location according to your choice.

2. Create Stored Procedure with UTL_FILE Package

Now, Create a Stored Procedure:

SQL> CREATE OR REPLACE PROCEDURE LOAD_FILE_DATA (File_Name IN VARCHAR2)
  2  IS
  3  vSFile UTL_FILE.FILE_TYPE;
  4  vNewLine VARCHAR2(2000);
  5  BEGIN
  6  vSFile := UTL_FILE.FOPEN ('IMPORTDIR',File_Name,'r');
  7  IF UTL_FILE.IS_OPEN(vSFile) THEN
  8  LOOP
  9  BEGIN
 10  UTL_FILE.GET_LINE(vSFile, vNewLine);
 11  IF vNewLine IS NULL THEN
 12  EXIT;
 13  END IF;
 14  INSERT INTO EMP_NAMES(ENAME,FILE_NAME)
 15  VALUES(vNewLine, File_Name);
 16  END;
 17  END LOOP;
 18  COMMIT;
 19  END IF;
 20  END LOAD_FILE_DATA;
 21  /

Procedure created.

3. Now run the procedure as following:

BEGIN

LOAD_FILE_DATA('EMP_NAME_LIST.csv');

END;

You can now check the Target Table must be populated with Data.

Another Example: 

In this Example, we will create a Stored Procedure which will accept Directory and File name as an input and will insert data into below Table.

NOTE: We will be using the same Directory which we have created in our previous example.

Source File: We have placed a source file inside DB Directory ‘IMPORTDIR’ Which is ‘F:\IMPORT. In this Example, we will be using LECTURER_DATA csv file.

Target Table: We will be loading data into below Table from above file.

NOTE: we will generate ID column inside Procedure itself. Or you can also use Sequecne to generate the same. Rest of the columns are present in CSV file.

CREATE TABLE LECTURER

(

  ID               NUMBER(5) PRIMARY KEY,

  First_Name       VARCHAR2(20),

  Last_Name        VARCHAR2(20),

  Major            VARCHAR2(30)

);

Create Stored Procedure with UTL_FILE Package: 
Now, create a Stored Procedure which will get data from the file reside in Defined Directory and load data into LECTURER Table.

SQL> CREATE OR REPLACE PROCEDURE LoadLecturerData(p_FileDir  IN VARCHAR2,p_FileName IN VARCHAR2)
  2  AS
  3    v_FileHandle UTL_FILE.FILE_TYPE;
  4    v_NewLine  VARCHAR2(100);  -- Input line
  5    MyFirstName Lecturer.First_Name%TYPE;
  6    v_LastName  Lecturer.Last_Name%TYPE;
  7    v_Major     Lecturer.Major%TYPE;
  8    v_FirstComma NUMBER;
  9    v_SecondComma NUMBER;
 10    p_TotalInserted NUMBER;
 11
 12  BEGIN
 13    v_FileHandle := UTL_FILE.FOPEN(p_FileDir ,p_FileName, 'r');
 14    p_TotalInserted := 1;
 15    LOOP
 16      BEGIN
 17      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
 18      EXCEPTION
 19        WHEN NO_DATA_FOUND THEN
 20      EXIT;
 21      END;
 22
 23      v_FirstComma := INSTR(v_NewLine, ',', 1, 1);
 24      v_SecondComma := INSTR(v_NewLine, ',', 1, 2);
 25
 26      MyFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
 27      v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1, v_SecondComma - v_FirstComma - 1);
 28      v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);
 29
 30      INSERT INTO LECTURER (ID, First_Name, Last_Name, Major) VALUES (p_TotalInserted, TO_CHAR(MyFirstName), TO_CHAR(v_LastName), TO_CHAR(v_Major));
 31      p_TotalInserted := p_TotalInserted + 1;
 32    END LOOP;
 33    UTL_FILE.FCLOSE(v_FileHandle);
 34    COMMIT;
 35  EXCEPTION
 36    WHEN UTL_FILE.INVALID_OPERATION THEN
 37      UTL_FILE.FCLOSE(v_FileHandle);
 38      RAISE_APPLICATION_ERROR(-20051, 'LoadLecturerData: Invalid Operation');
 39    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
 40      UTL_FILE.FCLOSE(v_FileHandle);
 41      RAISE_APPLICATION_ERROR(-20052, 'LoadLecturerData: Invalid File Handle');
 42    WHEN UTL_FILE.READ_ERROR THEN
 43      UTL_FILE.FCLOSE(v_FileHandle);
 44      RAISE_APPLICATION_ERROR(-20053, 'LoadLecturerData: Read Error');
 45    WHEN UTL_FILE.INVALID_PATH THEN
 46      UTL_FILE.FCLOSE(v_FileHandle);
 47      RAISE_APPLICATION_ERROR(-20054, 'LoadLecturerData: Invalid Path');
 48    WHEN UTL_FILE.INVALID_MODE THEN
 49      UTL_FILE.FCLOSE(v_FileHandle);
 50      RAISE_APPLICATION_ERROR(-20055, 'LoadLecturerData: Invalid Mode');
 51    WHEN UTL_FILE.INTERNAL_ERROR THEN
 52      UTL_FILE.FCLOSE(v_FileHandle);
 53      RAISE_APPLICATION_ERROR(-20056, 'LoadLecturerData: Internal Error');
 54    WHEN VALUE_ERROR THEN
 55      UTL_FILE.FCLOSE(v_FileHandle);
 56      RAISE_APPLICATION_ERROR(-20057, 'LoadLecturerData: Value Error');
 57    WHEN OTHERS THEN
 58      UTL_FILE.FCLOSE(v_FileHandle);
 59      RAISE;
 60  END LoadLecturerData;
 61  /

Procedure created.

Now run the procedure as following:

BEGIN

LoadLecturerData('IMPORTDIR','LECTURER_DATA.csv');

END;

You can now check the Target Table must be populated with Data.

SQL> SELECT * From LECTURER;

        ID FIRST_NAME           LAST_NAME            MAJOR
---------- -------------------- -------------------- ------------------------------
         1 FIRST_NAME           LAST_NAME            MAJOR
         2 Ravi                 Baluni               DATABASE
         3 Suryakant            Baluni               DATABASE
         4 Ankit                Joshi                JAVA
         5 Rajesh               Chand                QlikView
         6 Subho                Naha                 Unix
         7 Rajan                Kumar                Mainframe

7 rows selected.

Another Example: In this Example, we will use the below set of data and will use same Directory which we have used in earlier examples.

Source File: We have placed a source file inside DB Directory ‘IMPORTDIR’ Which is ‘F:\IMPORT. In this Example, we will be using EMPLOYEES_DATA csv file.

Target Table: We will be loading data into below Table from above file.

SQL> DESC EMP_DATA;

Name     Null     Type         
-------- -------- ------------ 
EMP_NO            VARCHAR2(10)       
ENAME    NOT NULL VARCHAR2(10) 
DEPTNO            VARCHAR2(10)            
HIREDATE          DATE         
DNAME             VARCHAR2(10) 
DLOC              VARCHAR2(10) 
SALARY            NUMBER(8,2)  
JOB_ID            VARCHAR2(20)

Create PL/SQL Block with UTL_FILE Package: Now, create a PL/SQL Block which will get data from the file reside in Defined Directory and load data into EMP_DATA Table.

DECLARE
 F UTL_FILE.FILE_TYPE;
 V_LINE VARCHAR2 (1000);
 V_EMPNO VARCHAR2(10);
 V_ENAME VARCHAR2(10);
 V_DEPTNO VARCHAR2(10);
 V_DNAME VARCHAR2(10);
 V_DLOC VARCHAR2(10);
 V_JOB_ID VARCHAR2(20);
BEGIN
F := UTL_FILE.FOPEN ('IMPORTDIR', 'EMPLOYEES_DATA.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_ENAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
  V_DEPTNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
  V_DNAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
  V_DLOC := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5);
  V_JOB_ID := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 6);

  INSERT INTO EMP_DATA VALUES(V_EMPNO, V_ENAME, V_DEPTNO, V_DNAME, V_DLOC, V_JOB_ID);
  COMMIT;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT;
  END;
 END LOOP;
 END IF;
 UTL_FILE.FCLOSE(F);
END;

You can now check the Target Table must be populated with Data.

SQL> SELECT Count(*) From EMP_DATA;

  COUNT(*)
----------
        52

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.