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.