http://slobaexpert.wordpress.com/2014/05/28/loading-csv-files-into-oracle-tables-using-plsql/

You can find the full details of this to above link, I used to visit it every day. 



There are various ways to load flat files into Oracle tables like using few popular GUI i.e. TOAD , PLSQL Developer, SQL Developer and many more..
But we can also use this by few other techniques such as using External tables or by using UTL_FILE package.
Here I am going to show how to use UTL_FILE package and read the CSV from the Oracle directory and load into the target table and finally rename the CSV by concatenating with sysdate.
By renaming the file it is always easier to track by file name with date or timestamp like when the source flat file was imported into the oracle table.
So to understand all the above mentioned we will have to go through a complete example.
Let us create a sample target table:
1
2
3
4
5
6
7
8
9
SQL> CREATE TABLE temp
 ( col1 NUMBER,
 col2 VARCHAR2(40),
 col3 DATE
 );
 
Table created.
 
SQL>
Create a CSV file with the below sample data:
1
2
3
4
5
COL1 COL2 COL3
1 AA 1-Jan-12
2 BB12 2-Feb-12
3 CCcc11 22-Aug-12
4 DD 22-Aug-12
Put the CSV file into the Oracle directory and if you do not have any directory then create it as like below:
1
2
3
SQL>CREATE OR REPLACE DIRECTORY ORA_DIR AS 'C:\temp'; -- Change the path as per your wish
 
SQL> GRANT READ, WRITE ON DIRECTORY ORA_DIR TO << Your User >>;
Now here is the Procedure to read the CSV file and load it into the temp table which we have created earlier and finally rename the source file with
<<File Name>><<_DD_MON_RR>>.csv  .
After so many comments to my older post , I thought of making it little better and generic way to load the CSV file into my tables. Earlier what I used to do is write the insert script for the target table and execute it to load the file.
Now if I have to load 3 files into 3 different tables then I have to write 3 different procedure to load the files but then thought why not just pass the table name and file name as a parameter and load it directly into my target table and then finally all my files would be renamed like concatenating with date .
Again it would be helpful if I have a table which will have the track of what files are been loaded and how many number of records are been passed along with that if I have a table to get the log like the discarded records then would be very helpful to do further analysis on why the records are getting failed.
Check if the user is having UTL_FILE privileges or not :
1
2
3
4
5
SQL> SELECT OWNER,
OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME = ‘UTL_FILE’
AND OWNER =<>;


If the user is not having the privileges then grant “UTL_FILE” to user from SYS user:
SQL> GRANT EXECUTE ON UTL_FILE TO <>;
To make use of the I have created two different tables as below: 
import_hist table and badlog table to track the history of the load and another to check the bad log if it occurs while doing the load .
Under the same user create an error log table to log the error out records while doing the import:
1
2
3
4
5
6
7
8
9
  
 
SQL> CREATE TABLE badlog
(
table_name VARCHAR2(200)
errm VARCHAR2(4000),
data VARCHAR2(4000) ,
error_date TIMESTAMP
);


Under the same user create Load history table to log the details of the file and tables that are imported with a track of records loaded:
1
2
3
4
5
6
7
8
  
SQL> create table IMPORT_HIST
(
FILENAME varchar2(200),
TABLE_NAME varchar2(200),
NUM_OF_REC number,
IMPORT_DATE DATE
);


One of my team member who is an intern ,took this as an challenge to make the correction on my blog and to make it very generic like what I mentioned for PIPE delimited files.
Thanks a ton Vani for your contribution…!!!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
CREATE OR REPLACE FUNCTION LOAD_CSV ( p_table in varchar2,
 p_dir in varchar2 DEFAULT 'ORA_DIR' ,
 P_FILENAME in varchar2,
 p_ignore_headerlines IN INTEGER DEFAULT 1,
 p_delimiter in varchar2 default ',',
 p_optional_enclosed in varchar2 default '"' )
 return number
 is
/***************************************************************************
-- PROCEDURE LOAD_CSV
-- PURPOSE: This Procedure read the data from a CSV file.
-- And load it into the target oracle table.
-- Finally it renames the source file with date.
--
-- P_FILENAME
-- The name of the flat file(a text file)
--
-- P_DIRECTORY
-- Name of the directory where the file is been placed.
-- Note: The grant has to be given for the user to the directory
-- before executing the function
--
-- P_IGNORE_HEADERLINES:
-- Pass the value as '1' to ignore importing headers.
--
-- P_DELIMITER
-- By default the delimiter is used as ','
-- As we are using CSV file to load the data into oracle
--
-- P_OPTIONAL_ENCLOSED
-- By default the optionally enclosed is used as '"'
-- As we are using CSV file to load the data into oracle
--
-- AUTHOR:
-- Sloba
-- Version 1.0
-- Vani (bobba.vani31@gmail.com)
-- Version 1.1
**************************************************************************/
 l_input utl_file.file_type;
 l_theCursor integer default dbms_sql.open_cursor;
 l_lastLine varchar2(4000);
 l_cnames varchar2(4000);
 l_bindvars varchar2(4000);
 l_status integer;
 l_cnt number default 0;
 l_rowCount number default 0;
 l_sep char(1) default NULL;
 L_ERRMSG varchar2(4000);
 V_EOF BOOLEAN := false;
 
 begin
 
 l_cnt := 1;
 for TAB_COLUMNS in (
 select column_name, data_type from user_tab_columns where table_name=p_table order by column_id
 ) loop
 l_cnames := l_cnames || tab_columns.column_name || ',';
 l_bindvars := l_bindvars || case when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then 'to_date(:b' || l_cnt || ',"YYYY-MM-DD HH24:MI:SS"),' else ':b'|| l_cnt || ',' end;
 
 l_cnt := l_cnt + 1;
 end loop;
 l_cnames := rtrim(l_cnames,',');
 L_BINDVARS := RTRIM(L_BINDVARS,',');
 
 L_INPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'r' );
 IF p_ignore_headerlines > 0
 THEN
 BEGIN
 FOR i IN 1 .. p_ignore_headerlines
 LOOP
 UTL_FILE.get_line(l_input, l_lastLine);
 END LOOP;
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 v_eof := TRUE;
 end;
 END IF;
 
 if not v_eof then
 dbms_sql.parse( l_theCursor, 'insert into ' || p_table || '(' || l_cnames || ') values (' || l_bindvars || ')', dbms_sql.native );
 
 loop
 begin
 utl_file.get_line( l_input, l_lastLine );
 exception
 when NO_DATA_FOUND then
 exit;
 end;
 
 if length(l_lastLine) > 0 then
 for i in 1 .. l_cnt-1
 LOOP
 
 dbms_sql.bind_variable( l_theCursor, ':b'||i,
 rtrim(rtrim(ltrim(ltrim(
 REGEXP_SUBSTR(l_lastline,'(^|,)("[^"]*"|[^",]*)',1,i),p_delimiter),p_optional_enclosed),p_delimiter),p_optional_enclosed));
 end loop;
 begin
 l_status := dbms_sql.execute(l_theCursor);
 l_rowCount := l_rowCount + 1;
 exception
 when OTHERS then
 L_ERRMSG := SQLERRM;
 insert into BADLOG ( TABLE_NAME, ERRM, data, ERROR_DATE )
 values ( P_TABLE,l_errmsg, l_lastLine ,systimestamp );
 end;
 end if;
 end loop;
 
 dbms_sql.close_cursor(l_theCursor);
 utl_file.fclose( l_input );
 commit;
 end if;
 insert into IMPORT_HIST (FILENAME,TABLE_NAME,NUM_OF_REC,IMPORT_DATE)
 values ( P_FILENAME, P_TABLE,l_rowCount,sysdate );
 
 UTL_FILE.FRENAME(
 P_DIR,
 P_FILENAME,
 P_DIR,
 REPLACE(P_FILENAME,
 '.csv',
 '_' || TO_CHAR(SYSDATE, 'DD_MON_RRRR_HH24_MI_SS_AM') || '.csv'
 ));
 commit;
 RETURN L_ROWCOUNT;
 end LOAD_CSV;
Here is the sample PLSQL block which will call “LOAD_CSV” procedure to read the flat file (i.e. CSV file) from the directory which I have used as ‘ORA_DIR’ and rename the source file:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE
 p_table VARCHAR2(200) := <>;
 p_dir VARCHAR2(200) := <>;
 p_filename VARCHAR2(200) := <>;
 v_return NUMBER;
BEGIN
 v_return := LOAD_CSV(p_table => p_table
 ,p_dir => p_dir
 ,p_filename => p_filename
 ,p_ignore_headerlines => p_ignore_headerlines
 ,p_delimiter => p_delimiter
 ,p_optional_enclosed => p_optional_enclosed);
 
 dbms_output.put_line(‘v_return = ‘ || v_return);
END;
You can modify the and utilize it …