How to Load data using PLSQL code?

In my previous article I have given the details of steps of how to load CSV data in to SQL tables also given details to load the data using database link. In this article I would like to give the details about Load data using PLSQL code. There are so many time we require to load the CSV data in the oracle SQL tables. In this article the focus is to load data using PLSQL code.

What are steps to load data using PLSQL code?

Step 1 : We require to check the permissions to File.

Most of the times the CSV file should not have the correct permission to load the data in SQL table or sometimes the directory does not have permission . The first step is to give the permission or rights to the specified directory using system dba.

C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 17 11:09:04 2022
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 Amit_CSV_file as 'c:\Amit_CSV_file\';
Directory created.

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

Step 2 : Loading File Table Creation.

To Load “Employee.CSV” file in to Oracle Table we have created a table T_EMP_DEPT as

C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 17 15:44:08 2022
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

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

Table created.

The above statements will create the T_EMP_DEPT with the multiple columns. Just make sure that the datatypes are similar to the CSV File created and also take care of the size of the datatypes as well. Sometimes user will get error regarding size of the datatype.

Step 3 : PLSQL Block writing using utility package.

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 ('Amit_CSV_file', 'EMPLOYEE.CSV', 'R'); --Read mode open
10    IF UTL_FILE.IS_OPEN(F) THEN  --File opened in read mode
11      LOOP
12        BEGIN
13          UTL_FILE.GET_LINE(F, V_LINE, 1000); --getting 1000 lines from file
14          IF V_LINE IS NULL THEN
15            EXIT;
16          END IF;
17          V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1); --find details regexp
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); --insert
22          COMMIT;
23        EXCEPTION  --exception handling
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 Arun             1000 20
      7499 Shanm            2000 30
      7521 AMIT             4000 30
      7566 RAMAN            2975 20
      7654 MARKOS           1250 30
      7698 HASAN            2850 30
      7782 OMKAR            2450 10
      7788 TIMBR            3000 20
      7839 PERNE            5000 10
      7844 KUSHAL           1500 30
   

10 rows selected.

I hope you like this article. If you find the article to load the data using PLSQL code useful kindly comment in comments section. There are also other ways to load the data using PLSQL code.