We need to move data from flat file to Oracle table frequently. For example sales department sends daily sale data in excel sheet to IT department, how this data feed into Oracle database (to tables) ? we will examine different methods.
1. SQLLOADER
sqlloader is an Oracle utility to load data from external files to table. This is one of the most used utility in Oracle database.
EMP
——- ——– ————
EMPNO NOT NULL NUMBER(38)
EMPNAME VARCHAR2(20)
SALARY NUMBER
DEPTNO NUMBER(38)
First create a control file with details of flat file and table column prameters
emp.ctl
LOAD DATA
INFILE ‘C:\Temp\emp.csv’
BADFILE ‘C:\Temp\emp.bad’
DISCARDFILE ‘C:\Temp\emp.dsc’
INSERT INTO TABLE emp
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ TRAILING NULLCOLS
(empno,empname,salary,deptno)
Flat file as emp.csv
10,Bill,12000,5
11,Solomon, 10000, 5
12,Susan, 10000, 5
13,Wendy, 9000 , 1
14,Benjamin, 7500, 1
15,Tom, 7600 , 1
16,Henry, 8500, 2
17,Robert, 9500, 2
18,Paul, 7700, 2
run below command in command prompt (Generally ORACLE_HOME\bin)
sqlldr userid=username/password@connect_string control=c:\temp\emp.ctl log=c:\temp\emp.log
Bad data will log into bad file. Note that sqlloader has many many options in it, many professionals use it to automate the loading process.
2. EXTERNAL TABLES
External tables is an advanced feature of Oracle SQLLOADER. You can write sql on top of the External Tables.
Step 1 :- Create directory ( with the help of DBA may be) and grant permissions to the user
sql>create directory load_dir as ‘C:\Temp’;
sql>grant read,write on directory load_dir to user;
Step 2 :- Create flat file in directory
emp.csv
10,Bill,12000,5
11,Solomon, 10000, 5
12,Susan, 10000, 5
13,Wendy, 9000 , 1
14,Benjamin, 7500, 1
15,Tom, 7600 , 1
16,Henry, 8500, 2
17,Robert, 9500, 2
18,Paul, 7700, 2
Step 3 :- Create EXTERNAL TABLE
Drop table EMP if it exists and then create it using below sql
CREATE TABLE emp
(EMPNO integer,
EMPNAME VARCHAR2(20),
SALARY integer,
DEPTNO integer)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
fields terminated by ‘,’
)
LOCATION (’emp.csv’)
);
Step 4 :- Run select query ( This will select data from External Table which is associated with a flat file )
Select * from emp;
10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2
3. UTL
UTL is another Oracle utility to load data from flat file to Oracle and vice versa.
Step1 :- Check the utl_directory using below sql (Ask DBA if you do not have priviliges)
Here “C:\TEMP”is the utl directory. So we can use any files in the directory for utl operations. I have an emp.dat file in it;
emp.dat
10,Bill
20,John
30,Bruce
USe below plsql block to read all lines from emp.dat
DECLARE
filehand UTL_FILE.FILE_TYPE;
line VARCHAR2(4000);
v_empno varchar2(10);
v_empname varchar2(10);
BEGIN
filehand := UTL_FILE.FOPEN(‘C:\TEMP’, ’emp.dat’, ‘R’);
LOOP
UTL_FILE.GET_LINE(filehand, line);
if line is not null then
v_empno := substr(line,1,instr(line,’,’)-1);
v_empname := substr(line,instr(line,’,’)+1,length(line));
dbms_output.put_line(v_empno);
dbms_output.put_line(v_empname);
insert into emp(empno,empname) values(v_empno,v_empname);
commit;
else
exit;
end if;
END LOOP;
commit;
utl_file.fclose(filehand);
EXCEPTION
WHEN others THEN
null;
END;
/
3 rows inserted into emp table.
4. Using Tools (SQLDEVELOPER)
EMP
——- ——– ————
EMPNO NOT NULL NUMBER(38)
EMPNAME VARCHAR2(20)
emp.csv
10,Bill
11,Solomon
12,Susan
13,Wendy
14,Benjamin
15,Tom
Step 1 – Click on Tables –> EMP
Step 2 – Click on Actions –> Import Data –> Choose csv file
Step 3 – Click next –> choose column details –> next
Data successfully loaded into EMP table
similarly TOAD also have
5. INSERT script
Data can be loaded using insert script. You can use excel to create dynamic insert scripts
Copy insert script from C column and run
| insert into EMP(empno,empname) values (11,’Solomon’); |
| insert into EMP(empno,empname) values (12,’Susan’); |
| insert into EMP(empno,empname) values (13,’Wendy’); |
| insert into EMP(empno,empname) values (14,’Benjamin’); |
| insert into EMP(empno,empname) values (14,’Tom’); |
Generally for large data sets, sqlloader and external tables are preferred methods.
( Note :- I have used very basic examples only to explain the basics of flat file loading, it may have some logical errors )
Like this:
Like Loading...
